CTE

[tshad]

I have read and seen that CTE can be slower than a SQL statement with a
derived table (I am not talking about recursion).

I had seen where this was said to be slower because SQL creates temp tables
internally. But I also saw this article by John Papa that says just the
opposite:

A CTE is a nice fit for this type of scenario since it makes the T-SQL much
more readable (like a view), yet it can be used more than once in a query
that immediately follows in the same batch. Of course, it is not available
beyond that scope. Additionally, the CTE is a language-level
construct-meaning that SQL Server does not internally create temp or virtual
tables. The CTE's underlying query will be called each time it is referenced
in the immediately following query.

Curious as to which is correct?

Thanks,

Tom

[Plamen Ratchev]

You cannot generalize that a CTE is slower/faster than derived table or plain
SQL statement. Both derived tables and CTEs are expanded in the query plan and
you can end up with the exact same execution plan. Only in some specific cases
you may have different plans. This is why it depends on each particular case.

--
Plamen Ratchev
http://www.SQLStudio.com

[tshad]

But that would only be the case if the CTE did not use temp tables to do its
work.

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:5auc86l0ahqp3m1fd...@4ax.com...

[Plamen Ratchev]

Any SQL statement (with or without CTE) can result in utilizing temp storage for
intermediate calculations. There is nothing specific to CTEs to make them
slower.

[Erland Sommarskog]

tshad (t...@dslextreme.com) writes:
> I have read and seen that CTE can be slower than a SQL statement with a
> derived table (I am not talking about recursion).

That sounds wrong in my eyes, because they are logically the same and
are implmented in the same way.

> I had seen where this was said to be slower because SQL creates temp
> tables internally.

To hold the intermediate the result of the CTE or derived table? So ein
Quatsch! No, that does not happen. The result of the CTE may not even
exist, because SQL Server may recast the computation order.

Now, note there is a bad side of this as well. If the CTE is referred to
in multiplce places in the query, you might prefer if SQL Server
materialised the result and reused it. What happens now is that
SQL Server effectively computes the CTE once for each reference.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx