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