p456 (12/9/2009)
Aren't most of your examples just syntactic sugar that could be done with a subquery?I would prefer to use simpler constructs if possible for ease of future maintenance.
From what I can see, there are only two situations where a Common Table Expression is really useful:
1) Where you need to join a subquery to itself, so you would otherwise need to either duplicate the subquery or use a temp table.
2) Where you want to use recursion.
Can anyone thing of any others?
Well you can JOIN to the same CTE multiple times in the same statement. Like I said it simplifies the GROUP BY thing. Here is the secret, at least to thinking about this. The CTE generates a very temporary table. It only lives for the length of the single statement. Could you do more with actual temp tables? Yes. Could you do as well with table valued functions? Maybe. How about stored procedures that return a table? Give it a shot. Do rhetorical questions bother the heck out of you? Sure they do! Like with everything else in SQL it's there if you want it.
ATBCharles Kincaid