Did you Know Table Variable Ignore Transactions?

They do, they are variables and are not affected by transactions, we must be careful when using them along with transactions, since it can lead to bad results.

Here is a quite common scenario that I have seen in production environment:

Insert data to a table, use OUTPUT to capture the records that were inserted, to finally do something weird with those records... I know, we do weird stuff with SQL Server.

If you are not familiar with OUTPUT, here is one of my previous post where I explain a little bit about it: SQL-How to capture the newly generated identity values after a multi-row insert?

The lab

Following script is very simple, please see below.

1. Create a table and a variable table

2. Open a transaction.

3. Insert some data and capture such data with OUTPUT.

4. Rollback the transaction

5. Show the data.

--\\ Drop the table
DROP TABLE IF EXISTS MyNewTable;

--\\ Create main table
CREATE TABLE MyNewTable (	ROW_ID INT IDENTITY(1,1) NOT NULL
						,	myValue_1 INT NULL
						,	myValue_2 INT NULL);

--\\DECLARE  @ Table
DECLARE  @MyAudit TABLE (	ROW_ID INT NOT NULL
						,	myValue_1 INT NULL
						,	myValue_2 INT NULL);

--\\Begin Transaction
BEGIN TRANSACTION;
--\\ INSERT DATA
INSERT INTO MyNewTable

	--\\ Output the data 
	OUTPUT INSERTED.ROW_ID
		,  INSERTED.myValue_1
		,  INSERTED.myValue_2 
	INTO @MyAudit

	-- \\ This generated the insertion.
	SELECT TOP 4
			myValue_1= ROW_NUMBER() OVER (ORDER BY a.name)% 4
		,	myValue_1= ROW_NUMBER() OVER (ORDER BY a.name)% 10
	FROM sys.all_objects a
	CROSS JOIN sys.messages c;

--\\Rollback
ROLLBACK;

--\\Show the data inserted in main table (remember transaction was rolled-back)
SELECT *
FROM MyNewTable;

--\\Show the data that was captured with Output
SELECT *
FROM @MyAudit;

Script will return the following results:

Main table does not return any rows due to RollBack, but the table variable was not affected by transactions.

If you have any questions or suggestions, please let me know in the comments.

Enjoy!!