By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,874 Members | 1,028 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,874 IT Pros & Developers. It's quick & easy.

The age old argument of Temp table vs Table variable

P: n/a
Hi All,

Hope someone can help me...

Im trying to highlight the advantages of using table variables as
apposed to temp tables within single scope.
My manager seems to believe that table variables are not advantageous
because they reside in memory.
He also seems to believe that temp tables do not use memory...

Does anyone know how SQL server could read data from a temp table
without passing the data contained therein through memory???

Is this a valid advantage/disadvantage of table variables VS temp
tables?

Jan 26 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Am 26 Jan 2007 05:43:42 -0800 schrieb Burbletrack:
Hi All,

Hope someone can help me...

Im trying to highlight the advantages of using table variables as
apposed to temp tables within single scope.
My manager seems to believe that table variables are not advantageous
because they reside in memory.
He also seems to believe that temp tables do not use memory...

Does anyone know how SQL server could read data from a temp table
without passing the data contained therein through memory???

Is this a valid advantage/disadvantage of table variables VS temp
tables?
Maybe this can help you a little bit:
http://support.microsoft.com/kb/305977/EN-US/

bye,
Helmut
Jan 26 '07 #2

P: n/a
Burbletrack (er**********@gmail.com) writes:
Im trying to highlight the advantages of using table variables as
apposed to temp tables within single scope.
My manager seems to believe that table variables are not advantageous
because they reside in memory.
He also seems to believe that temp tables do not use memory...

Does anyone know how SQL server could read data from a temp table
without passing the data contained therein through memory???

Is this a valid advantage/disadvantage of table variables VS temp
tables?
I could probably write several hundred lines about temp tables vs.
table variables, and you would still be confused.

I have been able to achieve radical performance enhancements by replacing
a temp table with a table variable. And I have been able to achieve
radical performance enhancements by replacing a table variable with
a temp table.

As for memory or not - that's a non-starter. A temp table is a real
table on disk, but if you query it, it will be brought into cache.
A table variable may in memory to start with - or may be not. But it
can spill to disk.

No, what is the overall important is that temp table has statistics,
table variables has not. Not having statistics means that they cannot
cause recompiles, which can be costly, particularly on SQL 2000 where
the entire procedure is always recompiled. But not having stastics
means that the optimizer will have to make standard assumptions which
can result in poor query plans.

If I am to give a recommendation it is that if you expect a small
number of rows, a few thousand may be, use a table variable. If you
exepct many rows use a temp table. But always be prepared to change
if the chosen strategy backfires.

Also, inserting into a table variable precludes parallelism. This
can sometimes be an issue.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 26 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.