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