Posted by Erland Sommarskog on 01/26/07 23:07

Burbletrack (ernst.geyser@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]