|
|
Posted by Erland Sommarskog on 09/08/06 07:47
DA Morgan (damorgan@psoug.org) writes:
> Following this from an Oracle perspective I am horrified at the use
> of these create-a-table on-the-fly methodologies and was hoping that
> the changes in SQL Server 2005 would render them a thing of the past.
Nah, there is indeed less need for temp tables these days. The addition
of derived tables in SQL 6.5 helped a lot.
But there are certainly cases where they still come in handy. If nothing
else, they make development simpler as you can use them as a work area.
And there are still situations where they can help performance. Say that
you have:
WITH CTE AS (SELECT ...)
SELECT CTE
JOIN CTE
WHERE NOT EXISTS (SELECT * FROM CTE ...)
While this syntax is neat and pure, the full story is that SQL Server
currently always compute the CTE for each instance. Thus, if the CTE is
complex, it may be a good idea to get the data into a temp table or
a table variable first.
> One question about them though ... how does the optimizer deal with
> them?
A temp table has stastistics like any regular table. If sufficiently many
rows changes, that will trigger a recompilation. This can be both a
blessing and a curse, not the least in SQL 2000 where recompilation
always hits the entire procedure.
Table variables does not have statistics, so for them the optimizer
will have to make standard assumptions.
--
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
Navigation:
[Reply to this message]
|