Posted by Alexander Kuznetsov on 12/10/05 00:06

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

Erland,

also sometimes we materialize intermediate results in a temporary
table, build some indexes on it, so that there are statisitcs on the
temp table. This way the optimizer must recompile, and it considers the
statistics on the temp table in the process. As a result, we may get a
much better plan and much faster execution.
Sometimes, not always, not often.

[Back to original message]