Table Variables and Transactions

Did you know that table variables are not affected by ROLLBACKs? After I discovered it in my testing, I searched Google and found many references to it. Apparently everyone knew about this besides me.

Here is a simple example:

-- Create two tables: one a temp table and one a table variable
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t  (Col1 int NOT NULL)
DECLARE @t TABLE (Col1 int NOT NULL)

-- Begin a transaction
BEGIN TRAN

-- INSERT two values into each table
INSERT INTO @t VALUES (1)
INSERT INTO @t VALUES (2)
INSERT INTO #t VALUES (1)
INSERT INTO #t VALUES (2)

-- ROLLBACK and check values
ROLLBACK TRAN

PRINT '#t'
SELECT * FROM #t
PRINT '@t'
SELECT * FROM @t

-- Just in case
IF @@TRANCOUNT > 0 ROLLBACK TRAN

Result:

#t
Col1
----

@t
Col1
----
   1
   2

The table variable (@t) is not affected by the ROLLBACK.

References:

• Comparing Table Variables with Temporary Tables
• Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance
• Table Variables In T-SQL