Posted by Erland Sommarskog on 12/09/05 22:22

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
> Same for temp tables - except very rare cases where the optimizer goofs
> up and creates a sub-optimal query plan, it's better to just write one
> complex query and let SQL Server figure out the best way to process it.

Sometimes you need to reuse a table expression in a query. In this case a
table variable or temp table may be a good idea to hold the intermediate
result, so that it is only computed once.

Notice that this also applies in SQL 2005 with regards to Common Table
Expressions. If you have:

WITH CTE (...) AS
SELECT ...
FROM CTE c1 ...
JOIN CTE c2 ...

SQL Server will computed the CTE twice, and thus only treat the CTE as a
macro.

--
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]