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