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 = 1SELECT @intVar BeforeTransactionBEGIN TRAN
SET @intVar = 2ROLLBACK
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 rowCREATE TABLE #TempTable (Col1 VARCHAR(100))INSERT INTO #TempTable (Col1)VALUES('Temp Table - Outside Tran');-- Create Table Variable and insert single rowDECLARE @TableVar TABLE(Col1 VARCHAR(100))INSERT INTO @TableVar (Col1)VALUES('Table Var - Outside Tran');-- Check the Values in tablesSELECT Col1 AS TempTable_BeforeTransactionFROM #TempTable;SELECT Col1 AS TableVar_BeforeTransactionFROM @TableVar;/*
Insert additional row in trans
Rollback Transaction at the end
*/BEGIN TRAN-- Insert single rowINSERT INTO #TempTable (Col1)VALUES('Temp Table - Inside Tran');-- Insert single rowINSERT INTO @TableVar (Col1)VALUES('Table Var - Inside Tran');ROLLBACK-- Check the Values in tablesSELECT Col1 AS TempTable_AfterTransactionFROM #TempTable;SELECT Col1 AS TableVar_AfterTransactionFROM @TableVar;GO-- Clean upDROP TABLE #TempTableGO

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.