DaveWentzel.com            All Things Data

Temporary Objects


Table Variables
1)they do get materialized in tempdb...contrary to popular myths.  Here is some test code to prove this...best to run on a quiet system if you are skeptical.
SELECT name FROM tempdb.sys.tables
WHERE name LIKE '%#%';
DECLARE @tbl TABLE(col1 varchar(20));
SELECT name FROM tempdb.sys.tables
WHERE name LIKE '%#%';
2)table variables are not visible to routines that the declaring routine invoked, which is different from temp tables. 
3)There are also no distribution statistics created/maintained.  This might be a good thing, depending.  You don't get recompilations associated with data change and autostats events. (Here's the proof)
4)You can't create explicit indexes, but by defining primary key or unique constraints when the table is declared you get indexes created implicitly...which also helps with performance. 
5)You can't use SELECT INTO, so you get a little more logging that using a temp table. 
6)they do not record activity in the transaction logs so they do not participate in transaction control.  If you ROLLBACK processing you do NOT rollback the contents of the table variables. (TSQL Autonomous Transactions or Another Reason I Like PLSQL over TSQL)

Add new comment