SQL server table variable vs temp table [duplicate]

Question

This question already has answers here:

Closed 14 years ago.

Possible Duplicate:
What's the difference between a temp table and table variable in SQL Server?

I am using a table variable to store the aggregate results of a query.

The query is as below

update @results 
set col1 = totals 
from ( select sum(x) as totals from ......) 
where id = 1

If I use a temp table the query runs much faster.

Should the use of table variable or temp table matter in the query above?

Am I overlooking something?

thanks

• user55474: i am not sure this is an exact duplicate of the question mentioned. the post given in the link mentions the perf differences with respect to record counts. However in my case that record count does not appear to be the factor as there is only one record being updated

Answer 1

It really depends on the amount of records. Table variables perform much better on smaller records sets. Here is a good blog post with some benchmarking: http://sqlnerd.blogspot.com/2005/09/temp-tables-vs-table-variables.html

• user55474: but i this case i am just updating just one record i.e the aggregate of the results of my inner query.
• Dustin Laine: How many records are in your table? The problem may not be the update, but the scanning of the table to find the record to update.
• user55474: the table variable has just 3 records so the amount of records is not the issue
• Dustin Laine: So a 3 record table with 1 update. How much time difference are you seeing?
• user55474: more than 5 second difference

Answer 2

table variables are fine until you get more than 100 results. If you are expecting 100 results or more then you should switch to a temp table for efficiency.

• marc_s: Why?? If a temp variable gets "too big", it will be stored into tempDB - just like a temp table. No real big difference here...
• Alexander Kahoun: That's deterministic of the environment, if memory is available then SQL Server will move the table back into memory. If you're constantly getting a large result it'll chew up the memory before storing it in the tempdb which does have a performance cost. This can be avoided all together by using a temp table to start with for large results. 100 results is just a rule-of-thumb, not a hard requirement.