SQL SERVER – Difference Temp Table and Table Variable – Effect of Transaction

Few days ago I wrote an article on the myth of table variable stored in the memory—it was very well received by the community. Read complete article here: SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth.

Today, I am going to write an article which follows the same series; in this, we will continue talking about the difference between TempTable and TableVariable. Both have the same structure and are stored in the database — in this article, we observe the effect of the transaction on the both the objects.

DECLARE @intVar INT
SET
@intVar = 1
SELECT @intVar BeforeTransaction
BEGIN TRAN
SET
@intVar = 2
ROLLBACK
SELECT
@intVar AfterRollBackTran

It is a very well known fact that variables are unaffected by transaction as their scope is very limited, and for the same reason, variables should be very carefully used. Let us see very a quick example below that demonstrates that there transactions do not affect the local variable.

Now let us carry out the same test on TempTable and Table Variables. If Table Variables are true variables, they should also demonstrate the same behavior. See the following example:

USE AdventureWorks
GO
-- Create Temp Table and insert single row
CREATE TABLE #TempTable (Col1 VARCHAR(100))
INSERT INTO #TempTable (Col1)
VALUES('Temp Table - Outside Tran');
-- Create Table Variable and insert single row
DECLARE @TableVar TABLE(Col1 VARCHAR(100))
INSERT INTO @TableVar (Col1)
VALUES('Table Var - Outside Tran');
-- Check the Values in tables
SELECT Col1 AS TempTable_BeforeTransaction
FROM #TempTable;
SELECT Col1 AS TableVar_BeforeTransaction
FROM @TableVar;
/*
Insert additional row in trans
Rollback Transaction at the end
*/
BEGIN TRAN
-- Insert single row
INSERT INTO #TempTable (Col1)
VALUES('Temp Table - Inside Tran');
-- Insert single row
INSERT INTO @TableVar (Col1)
VALUES('Table Var - Inside Tran');
ROLLBACK
-- Check the Values in tables
SELECT Col1 AS TempTable_AfterTransaction
FROM #TempTable;
SELECT Col1 AS TableVar_AfterTransaction
FROM @TableVar;
GO
-- Clean up
DROP TABLE #TempTable
GO

It is clear from example that just like any local variable table variable is not affected from transaction. This is very important detail to note as I have quite often seen developer using TempTable and TableVariables interchangeably without understanding their effect on transaction.

Let me know if you have any other tip which you think will be helpful to readers.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

35 thoughts on “SQL SERVER – Difference Temp Table and Table Variable – Effect of Transaction

  1. Hi Pinal,First of all i would say Good Morning it is 4:41am here in u.s.a,
    my question is if tablevariable does not rollback then is it saved at dick like regular tables or it flush out once your session ends or it flush out upon your restart of sql server?
    I think so upon ending of sessions but would like to confirm.

    Regard
    Iyasir

    Like

  2. Hi Pinal,First of all i would say Good Morning it is 4:41am here in u.s.a,
    my question is if tablevariable does not rollback then is it saved at disk(in database–datafile) like regular tables or it flush out once your session ends or it flush out upon your restart of sql server?
    I think so upon ending of sessions but would like to confirm.

    Regard
    Iyasir

    Like

    • Local variable can be of any type and can have only one value. Table varialbe is of type table and it just act like a table where you can have multiple columns with multiple rows

      Like

  3. Hi,

    All declared variables in SQL server are local variables and are available only in current session. There is nothing to specify the scope of variable. Table vriable is a variable of data type “Table”. Here Table does not specify the scope of variable but specify the data type of variable.

    Regards,
    Pinal Dave

    Like

  4. hi Pinal Dave

    i do have a temporary table definition in a variable.

    how can i created this table ?
    can i use exec @var?(var contains definition of temp table )

    I need to do so because no of columns depends on user input

    Please suggest me

    Like

  5. Hello Feroz,

    You can create temp table using EXEC statement but that would be available only in EXEC statement. For example:

    EXECUTE ( ‘CREATE TABLE #SalesTbl
    (SalesID int, SalesName varchar(10)) ;
    INSERT INTO #SalesTbl VALUES(1, ”ss”)
    SELECT * FROM #SalesTbl’) — success

    SELECT * FROM #SalesTbl — Error

    Because EXEC starts a new session and temp table created inside that session is not accessible outside the EXEC () script. If you create a global table than that would be accessible as below:

    EXECUTE ( ‘CREATE TABLE ##SalesTbl
    (SalesID int, SalesName varchar(10)) ;
    INSERT INTO ##SalesTbl VALUES(1, ”ss”)
    SELECT * FROM ##SalesTbl’ ) — success
    SELECT * FROM ##SalesTbl — success

    Regards,
    Pinal Dave

    Like

  6. hi Pinal Dave

    is there any other alternative to my problem as i’m s/w developer and the global temporary table will be accessed by other user also.

    i’m having definition in a variable like

    DeclARE @sql varchar(100)
    set @sql=‘CREATE TABLE #SalesTbl
    (SalesID int, SalesName varchar(10)) ;)
    exec(@sql)

    now i want to insert records?
    this temporary table is expired
    if i will use global temporary then it will be shared by other users

    so Please suggest me

    Like

  7. Hi Feroz,

    Append all insert statment to variable @sql and then execute. As below

    @sql = @sql + ‘INSERT INTO #SalesTbl VALUES(,,,) ‘
    @sql = @sql + ‘INSERT INTO #SalesTbl VALUES(,,,) ‘
    exec (@sql)

    Regards,
    Pinal Dave

    Like

  8. i have 2 temporary tables with these columns
    file_no
    amount
    date
    and another table
    file_no
    day1
    day2
    .
    .
    .
    day31

    what i have to do is select the day part of the date(suppose 12/18/2009 i have to select 18) and put the value of amount into another tables column of that day (suppose day18)
    is there any procedure to overcome this problem

    Like

  9. this table is created dynamically using
    DeclARE @sql varchar(100)
    set @sql=‘CREATE TABLE #SalesTbl
    (file_no int,amount int,date datetime) ;)
    exec(@sql)

    file_no
    amount
    date

    is the above procedure possible

    Like

    • I think Pinal already answered you. But here’s how you can do it. Take notice that you must set the all the statements into @sql variable and then execute is as whole. You can’t execute them separately because #SalesTbl is destroyed right after the exec command returns.

      create procedure foo
      as
      begin
      declare @sql varchar(100)

      set @sql=’CREATE TABLE #SalesTbl(file_no int,amount int,date datetime); ‘
      set @sql+=’SELECT * FROM #SalesTbl’

      exec(@sql)
      end

      exec dbo.foo
      drop procedure foo

      Like

  10. hi Pinal Dave

    as like you said that i did below that but i got the error in the @TableVar

    –Create Table Variable and insert single row

    DECLARE @TableVar TABLE(Col1 VARCHAR(100))
    INSERT INTO @TableVar (Col1)
    VALUES(‘Table Var – Outside Tran’);

    SELECT Col1 AS TableVar_BeforeTransaction
    FROM @TableVar

    THis is the error

    Msg 1087, Level 15, State 2, Line 2
    Must declare the table variable “@TableVar”.

    can you explain that why error will come
    Thanks & Regards,
    D.Ponraj

    Like

  11. Hi Ponraj,

    You need to make sure that you are executing all these queries at once.

    This is table variable, so we need to declare at every execution time.

    So here I think what did you do is:

    First execute declare and insert statements and then you try to execute “select”. IN Table variable, you need to execute all statements at once then only you will get an actual result.

    Scope of table variable is per execution.

    Thanks,

    Tejas

    Like

  12. I like the article. Most of the time I request developers to use table variables if the number of rows processing is small. However, I would like to know the benchmark of using table variable instead of temp table. Please share if any have have Idea about this.

    Like

  13. Hi Pinal,

    I want to create table by the using procedure and pass it table name as variable as like below.

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE [dbo].[ProcInsTab]
    @Emp_ID varchar(50),
    @Password varchar(max),
    @Emp_Name varchar(100),
    @Address varchar(250),
    @City varchar(50),
    @Mobile_No varchar(10)
    AS

    CREATE TABLE @Emp_ID(
    [Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Catalog Version] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Mailed] [float] NULL ,
    [Sales] [money] NULL ,
    [Production $] [int] NOT NULL ,
    [Orders] [float] NULL ,
    [Response] [float] NULL ,
    [Response of Test Control] [int] NOT NULL ,
    [Average Invoice] [float] NULL ,
    [SMP] [float] NULL ,
    [SMP of Test Control] [int] NOT NULL ,
    [Catalog Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Brand] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Drop Date] [smalldatetime] NULL
    ) ON [PRIMARY];
    INSERT INTO [Employee_Master]
    ([Emp_ID]
    ,[Password]
    ,[Emp_Name]
    ,[Address]
    ,[City]
    ,[Mobile_No])
    VALUES
    (@Emp_ID, @Password,@Emp_Name, @Address,@City, @Mobile_No);

    Please help me how to solved this problem.

    Like

  14. Hello Ravi,

    Prepare the CREATE TABLE statement dynamically to create global or permanent table because temporary or table variable would not be accessible outside of execution statement.

    Regards,
    Pinal Dave

    Like

  15. Hi Pinal Dave,

    I have modified the above query by adding one more variable to store the SQL Statement in SQLString,
    When executed, I got the error in the @TableVar

    — Start —

    DECLARE @TableVar TABLE(Col1 VARCHAR(100))
    DECLARE @SqlString as varchar(max)

    SET @SqlString = ‘INSERT INTO @TableVar (Col1) VALUES(”Table Var – Outside Tran”)’

    EXEC (@SqlString)

    SET @SqlString = ‘SELECT Col1 AS TableVar_BeforeTransaction FROM @TableVar’

    EXEC (@SqlString)

    — End —

    Msg 1087, Level 15, State 2, Line 1
    Must declare the table variable “@TableVar”.
    Msg 1087, Level 15, State 2, Line 1
    Must declare the table variable “@TableVar”.

    ——————————————————–

    Please help me how to solved this problem.

    Like

  16. Hi Sanjay,

    Everytime we execute a batch in EXEC function a new session is created and tem tables and table variables created in earlier session becomes unaccessible. If you create a single batch then you can get the desired result as below:

    DECLARE @SqlString as varchar(max)
    SET @SqlString = ‘DECLARE @TableVar TABLE(Col1 VARCHAR(100))’
    SET @SqlString = @SqlString + ‘INSERT INTO @TableVar (Col1) VALUES(”Table Var – Outside Tran”)’
    EXEC (@SqlString)
    SET @SqlString = @SqlString + ‘SELECT Col1 AS TableVar_BeforeTransaction FROM @TableVar’
    EXEC (@SqlString)

    Regards,
    Pinal Dave

    Like

  17. Hi Pinal,

    I want to create table permanent by the using procedure and pass it table name as variable as like below.

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE [dbo].[ProcInsTab]
    @Emp_ID varchar(50),
    @Password varchar(max),
    @Emp_Name varchar(100),
    @Address varchar(250),
    @City varchar(50),
    @Mobile_No varchar(10)
    AS

    CREATE TABLE @Emp_ID(
    [Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Catalog Version] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Mailed] [float] NULL ,
    [Sales] [money] NULL ,
    [Production $] [int] NOT NULL ,
    [Orders] [float] NULL ,
    [Response] [float] NULL ,
    [Response of Test Control] [int] NOT NULL ,
    [Average Invoice] [float] NULL ,
    [SMP] [float] NULL ,
    [SMP of Test Control] [int] NOT NULL ,
    [Catalog Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Brand] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Drop Date] [smalldatetime] NULL
    ) ON [PRIMARY];
    INSERT INTO [Employee_Master]
    ([Emp_ID]
    ,[Password]
    ,[Emp_Name]
    ,[Address]
    ,[City]
    ,[Mobile_No])
    VALUES
    (@Emp_ID, @Password,@Emp_Name, @Address,@City, @Mobile_No);
    Please help me how to solved this problem.

    Like

  18. Hi pinal,
    Since table variables are unaffected by transaction, does this means that they bypass completely the tempdb log?
    The reason why I am asking is, since I make very heavy use of memory tables, so my question is if putting the tempdb log file on its own array will improve performance?

    Like

    • I have conducted an experiment to see it uses tempdb’s log.
      On a SQL Server instance that I am the sole user of it, I have first restarted SQL Server, and I have checked the tempdb log which was at that time 512 kb.
      Then I have executed the following query against one of the databases on the instance:

      DECLARE @i int
      DECLARE @a table (i int)
      SET @i = 0
      WHILE @i < 100000
      BEGIN
      INSERT INTO @a values(@i)
      SET @i = @i + 1
      END

      After which I have checked again the log file size and it was now 1536 kb.
      So this shows us, that although table variables are not being rolled back, and although the log of tempdb is not used to redo anything anyway (since at startup tempdb is recreated), still any write to table variables is being logged to tempdb's log.
      And this is something that deems to be unnecessary, especially due to the fact that SQL Server has to for writes to the log, and if SQL Server would not write table variables tot he log then there would probably be a performance gain.
      As such I would say that for an OLAP server, I would rather have an extra spindle for the tempdb log rather then for the database log, since for reads the tempdb log perfomance is more of a concern.

      Like

  19. Pingback: SQL SERVER – Debate – Table Variables vs Temporary Tables – Quiz – Puzzle – 13 of 31 « SQL Server Journey with SQL Authority

  20. Pingback: SQL SERVER – Table Variables and Transactions – SQL in Sixty Seconds #007 – Video « SQL Server Journey with SQL Authority

  21. Pingback: SQL SERVER – Using MAXDOP 1 for Single Processor Query – SQL in Sixty Seconds #008 – Video « SQL Server Journey with SQL Authority

  22. Pingback: SQL SERVER – Weekly Series – Memory Lane – #009 « SQL Server Journey with SQL Authority

  23. Hi

    I am following your articles from quite a while and honestly learn alot from examples you always gave in your articles. I have a situation in which I am passing table name as a parameter and want to insert the information generated from select statement into the table but I am keep getting an errors, stating must declare as a table variable. If I declare as a table variable it stores the information into table variable and not insert the data into the table I want to take a look on below example

    declare @tableName Varchar(100)
    set @@tableName =’smtpF2.dbo.infoChange';
    Create table #temp
    (
    change_version_state varchar(max),
    change_version_status varchar(200),
    uniqueid varchar(20),
    Country_code varchar(15),
    Research_date varchar(15),
    data_date varchar(14),
    Make varchar(50),
    Model varchar(50),
    Versions varchar(255),
    Model_year varchar(15),
    doors varchar(10),
    body varchar(10));
    insert into #temp select * from infoChange
    select * from #temp
    declare @tableNames Varchar(100)
    set @tableNames =’smtpF2.dbo.infoChange';
    insert into @tableNames select * from #temp

    Your urgent reply / advise will be very appreciated
    Thanks

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s