Effect of Rollback Transaction on Table Variable in SQL Server

One important difference between table variables and temporary tables is that ROLLBACK command do not affect table variables.

We will create a temporary table and insert one value into the table. After that we will begin a transaction which inserts another value into the table, and then ROLLBACK the transaction:

CREATE TABLE #T_Tab
(Identity_Column INT IDENTITY(1,1),
Test_Data int)
INSERT INTO #T_Tab (Test_Data) VALUES (1);
BEGIN TRANSACTION
INSERT INTO #T_Tab (Test_Data) VALUES (2)
ROLLBACK TRANSACTION
SELECT * FROM #T_Tab
DROP TABLE #T_Tab

As expected, there will be only one record in the temporary table, the second insert is rolled back:

temp_table_rollback

Then, we will create a table variable and insert one value into it. After that we will begin a transaction which inserts another value into the table variable, and then ROLLBACK the transaction:

DECLARE @T_Var TABLE
(Identity_Column INT IDENTITY(1,1),
Test_Data int)
INSERT INTO @T_Var (Test_Data) VALUES (1);
BEGIN TRANSACTION
INSERT INTO @T_Var (Test_Data) VALUES (2)
ROLLBACK TRANSACTION
SELECT * FROM @T_Var

As we can see, there are two records in the table variable, because transaction rollbacks do not affect table variables:

table_variable_rollback