Common Table Expressions

CTE's appear to have 2 advantages:
1. Compliance with ANSI
2. Recursive queries.
Aside from these reasons, and perhaps a syntax preference by some, I do not
see a good reason to go out of my way to use them. The remaining situations
don't seem to give any benefit:
1. If I wanted to use the results of the CTE more than once, and the results
of the CTE did not change, I might as well use a temporary table so the CTE
doesn't have to be executed more than once.
2. In situations where I only use the CTE once, the CTE could frequently be
integrated into the existing query that leverages the CTE.
Comments? Thanks.
MarkMark,
Please see:
http://www.aspfaq.com/sql2005/show.asp?id=1
HTH
Jerry
"Mark" <mark@.nojunkmail.com> wrote in message
news:%23NoYQJC1FHA.3376@.TK2MSFTNGP14.phx.gbl...
> CTE's appear to have 2 advantages:
> 1. Compliance with ANSI
> 2. Recursive queries.
> Aside from these reasons, and perhaps a syntax preference by some, I do
> not see a good reason to go out of my way to use them. The remaining
> situations don't seem to give any benefit:
> 1. If I wanted to use the results of the CTE more than once, and the
> results of the CTE did not change, I might as well use a temporary table
> so the CTE doesn't have to be executed more than once.
> 2. In situations where I only use the CTE once, the CTE could frequently
> be integrated into the existing query that leverages the CTE.
> Comments? Thanks.
> Mark
>