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?

Charles Kincaid (12/9/2009)

---

Well you can JOIN to the same CTE multiple times in the same statement.

Like I said it simplifies the GROUP BY thing.

SELECT SalesOrderID, S.CustomerID, CountOfSales, AvgSale, LowestSale, HighestSale 
FROM (
    SELECT COUNT(*) AS CountOfSales, AVG(TotalDue) AS AvgSale, 
        MIN(TotalDue) AS LowestSale, MAX(TotalDue) AS HighestSale,
        CustomerID 
     FROM Sales.SalesOrderHeader
     GROUP BY CustomerID)
) csales
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.

Charles Kincaid (12/9/2009)

---

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

Kathi Kellenberger (12/9/2009)

---

At least for me, I have really liked using CTEs because it makes the query easier to read and I have seen performance improvements depending on the situation.