Temp table vs Table variable

[tc3596]

I've read many posts that recommend using table variables over Temp tables. In my brief tests between the two, I have found that temp tables are indeed quicker. My test was this. I made a copy of one stored proc that used a temp table. On the copy version I edited it to use table variables instead. Running both stored procs from query analyzer, the one with temp variables ran in 2 seconds and the other in 8 seconds. I repeated this several times, all with the same result. So, what am I missing here? Aren't table variables supposed to be faster?

 

[JayKusch]

Here is a post to chew on ...

http://www.winnetmag.com/SQLServer/Forums/messageview.cfm?catid=1666&threadid=114817

Thanks

J. Kusch

 

[nigelrivett]

Table variables take less resourcds to create so for small sets of data on many threads (often the case with web apps) they should be faster.
Temp tables give the optimiser more options so for large infrequently used tables they can be faster.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[tektipdjango]

The table variable is a great way but don't forget to put the primary and unique constraints in the right columns. Otherwise the following calculations will not be efficient, as the optimizer cannot know itself at table creation the use that will be done of it afterwards!
One useful app of table variables is in function that return a table.

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​