SQL server table variable vs temp table

Question

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

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 — user55474, May 10 '11 at 02:36

score 1 · Answer 1 · answered May 09 '11 at 18:44

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

answered May 09 '11 at 18:44

Dustin Laine

37,935
10
86
125

but i this case i am just updating just one record i.e the aggregate of the results of my inner query. – user55474 May 09 '11 at 18:50
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. – Dustin Laine May 09 '11 at 18:52
the table variable has just 3 records so the amount of records is not the issue – user55474 May 09 '11 at 18:58
So a 3 record table with 1 update. How much time difference are you seeing? – Dustin Laine May 09 '11 at 19:02
more than 5 second difference – user55474 May 09 '11 at 19:13

score 0 · Answer 2 · answered May 09 '11 at 18:44

0

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.

answered May 09 '11 at 18:44

Alexander Kahoun

2,458
24
36

2

Why?? If a temp variable gets "too big", it will be stored into `tempDB` - just like a temp table. No real big difference here... – marc_s May 09 '11 at 18:51
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. – Alexander Kahoun May 09 '11 at 19:19

SQL server table variable vs temp table

2 Answers2