SQL Server 2000 temp table vs table variable

Question

What would be more efficient in storing some temp data (50k rows in one and 50k in another) to perform come calculation. I'll be doing this process once, nightly.

How do you check the efficiency when comparing something like this?

Answer 1

The results will vary on which will be easier to store the data, in disk (#temp) or in memory (@temp).

A few excerpts from the references below

• A temporary table is created and populated on disk, in the system database tempdb.
• A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.
• Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.
• [Y]ou can create indexes on the temporary table to increase query performance.

Regarding your specific case with 50k rows:

As your data size gets larger, and/or the repeated use of the temporary data increases, you will find that the use of #temp tables makes more sense

References:

• Should I use a #temp table or a @table variable?
• MSKB 305977 - SQL Server 2000 - Table Variables

• brendan: In my experience 50K would be too much for a table var but I don't have hard data to back that. I typically try to keep table vars < 5K.
• Joe: You can create additional indexes on # tables whereas you can't on vars.
• SQLMenace: A temp variable will overflow to disk also when you pump enough data into it, 50K rows will push it to disk partially
• boflynn: Thanks for the note, Joe. I added a note about indices.
• wqw: Two benefits of indexes on temp tables - these can be non-unique (while table vars are only unique through constraints) - but the single biggest advantage comes from unexpected ally: statistics are "cheap" indexes and these are auto created on temp tables by the query optimizer (pstatman) -- makes a huge difference.

Answer 2

There can be a big performance difference between using table variables and temporary tables. In most cases, temporary tables are faster than table variables. I took the following tip from the private SQL Server MVP newsgroup and received permission from Microsoft to share it with you. One MVP noticed that although queries using table variables didn't generate parallel query plans on a large SMP box, similar queries using temporary tables (local or global) and running under the same circumstances did generate parallel plans.

More from SQL Mag (subscription required unfortunately, I'll try and find more resources momentarily)

EDIT: Here is some more in depth information from CodeProject

• Saif Khan: Thanks for the tip. I came across the codeproject article just after posting this question.