Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Table Variables and Transactions

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:



trantest1



However in the first case you get:



trantest2



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


 



trantest3



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).

Comments

Posted by Jack Corbett on 21 September 2010

Steve,

This isn't a BOL reference, but it does say that table variables aren't affected by rollbacks.  support.microsoft.com/.../305977

Posted by Steve Jones on 21 September 2010

Thanks, that's about what I was looking for.

Posted by Jason Brimhall on 22 September 2010

Thanks Jack for the reference

Thanks Steve for the article.

Posted by essam.computer on 25 September 2010

Thanks Steve for the article

Leave a Comment

Please register or log in to leave a comment.