temp table (#), global temp tables (##) and @table variables
I've been working "full-time" on TSQL scripts for the past month (no
with .NET windows/web apps) and mostly on optimization. And I feel that
I should share with everyone this article about temp tables and table
variables and some of my own notes. Go read the article below then you
may come back here. Take careful note of the Conclusion part at the end
of the article. [more]
Should I use a #temp table or a @table variable?
Things to generally consider are speed, disk space and cpu
utilization, persistence. And here are some short hopefully helpful
notes.
1) persistence - if you need the data to persist even after the execution then no doubt you need to use permanent tables.
2) cpu utilization - always review indexes. effect of the use of
regular temp, global temp tables, table variables to performance is not
as significant as a missing index. Among other things, it is safe to
say, always ensure you have a primary key. and also if you will perform
queries with ORDER BY then always consider your clustered indexes are
correct. you can use estimated or actual execution plans to analyze
your queries. I recommend SQL profiler too but correct me if I'm wrong
but they are only able to profile permanent tables.
3) speed - first thing, indexes again too (see above). Secondly and
very important - Although table variables may seem (and actually
common) faster than temp tables, my observation is that if you are
dealing with large datasets then temp tables are way way faster than
table variables. I could not quantify giving the dataset I'm working on
but suffice to say that it took quite a number of folds faster that I
cancelled the execution.
4) disk space - temp tables, global temp tables and table variables
take up the same space as permanent tables would. But they should be
cleared once the procedure/function goes out of scope. The tempdb
transaction log is less impacted than with #temp tables;
table variable log activity is truncated immediately, while #temp table
log activity persists until the log hits a checkpoint, is manually
truncated, or when the server restarts
FROM: .NET Developer Notes on temp table (#), global temp tables (##) and @table variables