Table Variable and Rollback

I am currently at PASS Summit in Seattle and learning a ton of new stuff.  There are a lot things that I have learned so far, but one thing really jumped out at me.  I attended Brent Ozar’s session on Cardinality Estimates and he showed that when you use table variables, the transaction does not get rolled back. That is what is great about SQL Server, there is always something to learn!!

If you run this code, you will see that when the select runs, it will actually not return any rows.

CREATE TABLE #test
(Name VARCHAR(100))

BEGIN TRAN
    INSERT #Test
(Name)
VALUES
(‘Dave’),
(‘Raf’)

ROLLBACK

SELECT * FROM #test

DROP TABLE #test

However, when you run this code, you will see that that rows are still in the variable even though the rollback command has completed.

DECLARE @TestV TABLE
(Name VARCHAR(100))

BEGIN TRAN
        INSERT @Testv
(Name)
VALUES
(‘Dave’),
(‘Raf’)

ROLLBACK

SELECT * FROM @TestV

This is another reason why table variables should be used with caution. Table variables do not get statistics on the columns which of course can be a performance killer!!!