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.