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

SQL SERVER - Difference Temp Table and Table Variable - Effect of Transaction tran

Solarwinds

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

SQL SERVER - Difference Temp Table and Table Variable - Effect of Transaction tran1

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 (https://blog.sqlauthority.com)

Solarwinds

, , ,
Previous Post
SQL SERVER – Download FREE SQL SERVER Express Edition and Service Pack 1
Next Post
SQL SERVER – Get Date of All Weekdays or Weekends of the Year

Related Posts

34 Comments. Leave new

  • 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?

    Reply
    • 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.

      Reply
      • (the last answer) claims that table variables are not being written to the log, and this is the reason why they are not participating in transactions, however as I have showed this appears to be wrong.

  • Thanks

    Reply
  • 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

    Reply
    • Please ignore the first two lines as it was mistakenly copied (temp table declaration on top) example starts from create…
      Thanks

      Reply
  • Thanks Pinal.

    Reply
  • Hello, Could anyone explain about merge in sql ? what work it does. Plz be openly

    Reply
  • SIR WHERE ARE THE TABLE VARIABLE STORED…….IN THE MEMORY OR DISK…AND HOW?

    Reply
  • Hello pinal, can you explain me, where table variable stored exactly, I have confused because some where i read that they are stored in tempdb and some where i read they stored in memory……I created table variable but i can’t find in tempdb. Please clear my confusion…………..

    Reply

Leave a Reply

Menu