Charles Kincaid (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 think of any others?
Well you can JOIN to the same CTE multiple times in the same statement.
That's what I mean by point 1.
Like I said it simplifies the GROUP BY thing.
Couldn't you just do that with a standard subquery?
SELECT SalesOrderID, S.CustomerID, CountOfSales, AvgSale, LowestSale, HighestSale
SELECT COUNT(*) AS CountOfSales, AVG(TotalDue) AS AvgSale,
MIN(TotalDue) AS LowestSale, MAX(TotalDue) AS HighestSale,
GROUP BY CustomerID)
INNER JOIN Sales.SalesOrderHeader AS S
ON S.CustomerID = csales.CustomerID;
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.
Yes, but you can also think of a standard subquery as generating a very temporary "table", in exactly the same way.
I can't see that the CTE gives you anything extra other than the two situations I listed.