The content of table variables is retained even when a transaction is rolled back

You learn something new every day! Thanks to the superbly detailed answer by Martin Smith on the question What's the difference between a temp table and table variable in SQL Server? at Database Administrators StackExchange I've learnt that the query:

 DECLARE @T TABLE(X INT)
CREATE TABLE #T(X INT)

BEGIN TRAN

INSERT #T
OUTPUT INSERTED.X INTO @T
VALUES(1),(2),(3)

/*Both have 3 rows*/
SELECT *, 'IN TRAN #T' FROM #T
SELECT *, 'IN TRAN @T' FROM @T

ROLLBACK

/*Only table variable now has rows*/
SELECT *, 'AFTER ROLLBACK #t' FROM #T
SELECT *, 'AFTER ROLLBACK @T' FROM @T
DROP TABLE #T

When run against SQL Server will show rows from the final SELECT statement, which I did not expect. This may be common knowledge, but somehow over more than a decade of using SQL Server, I've managed to miss this. One for the toolbox.

About Rob

I've been interested in computing since the day my Dad purchased his first business PC (an Amstrad PC 1640 for anyone interested) which introduced me to MS-DOS batch programming and BASIC.

My skillset has matured somewhat since then, which you'll probably see from the posts here. You can read a bit more about me on the about page of the site, or check out some of the other posts on my areas of interest.

No Comments