Author: way0utwest

I actually had a question of the day submitted on SQLServerCentral about table variables and transactions, but the person didn’t have a reference for it. So I had to go digging around to find one. It doesn’t seem to be documented in BOL, but numerous MVPs and MS employees have posted about this behavior (by design) in places.

Here’s the code I saw:

DECLARE @MyTable TABLE 

  ( MyIdentityColumn INT IDENTITY(1,1),
    MyCity NVARCHAR(50))
INSERT INTO @MyTable (MyCity) VALUES (N'Boston');
BEGIN TRANSACTION IdentityTest
INSERT INTO @MyTable (MyCity) VALUES (N'London')
ROLLBACK TRANSACTION IdentityTest
INSERT INTO @MyTable (MyCity) VALUES (N'New Delhi');
SELECT * FROM @MyTable mt

What do you expect from that? What about this code?

CREATE TABLE TranTest 
 ( MyIdentityColumn INT IDENTITY(1,1),
   MyCity NVARCHAR(50))
GO
INSERT INTO TranTest (MyCity) VALUES (N'Boston');
BEGIN TRANSACTION IdentityTest
INSERT INTO Trantest (MyCity) VALUES (N'London')
ROLLBACK TRANSACTION IdentityTest
INSERT INTO TranTest (MyCity) VALUES (N'New Delhi');
SELECT * FROM TranTest mt

In the second case, you’d expect two rows, right? Something like this:

However in the first case you get:

The insert with “London” isn’t rolled back. This is because the table variable doesn’t participate in transactions. You can see this with this update as well.

DECLARE @MyTable TABLE (MyIdentityColumn INT IDENTITY(1,1),
 MyCity NVARCHAR(50))
INSERT INTO @MyTable (MyCity) VALUES (N'Boston');
BEGIN TRANSACTION IdentityTest
UPDATE @MyTable SET MyCity = 'Denver'
ROLLBACK TRANSACTION IdentityTest
INSERT INTO @MyTable (MyCity) VALUES (N'New Delhi');
SELECT * FROM @MyTable mt

You might think this is a bug, after all, a transaction is supposed to capture changes and enforce the ACID principles. That is true, but a table variable isn’t a permanent change on the database. It’s a temporary object that exists only in memory, and only for the duration of the batch.

This means that if you want to persist anything from a table variable, you need to write it to a real table, which will enforce ACID principles.

Where do you need this? It’s extremely handy for capturing information about potential issues in a transaction, like logging, and returning them outside of the transaction (where/when you can store them in a real table).