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

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