Aside from that, CTE's cannot be used as a total replacement of temp tables which are derived from more complex statement as you cannot nest CTE's and cannot use two CTE's in one SQL statement.

Walter, I didn't initially read this sentence in full, until Naomi replied. Again, yes, CTEs are not a full replacement for temp tables. However, that last part ("cannot nest CTEs and cannot use two CTEs in one SQL statement") is definitely not true.

I refer you to a message I posted about a month ago, Message #1593237
It's an example I use when showing how to aggregate in a scenario of one master table and multiple child tables. You can create multiple CTEs, delimited by a comma, and then reference them in the final SELECT.

(Yes, you can accomplish the same solution with actual inline derived table subqueries)

;with POSales as   -- CTE 1
        ( select ShipMethodID, sum(TotalDue)  as POTotal 
		        from Purchasing.PurchaseOrderHeader 
				       group by ShipMethodID) ,   -- note the 2 CTEs, separated by a comma
   SOSales as      -- CTE 2
        (Select ShipMethodID, sum(TotalDue) as SOTotal 
		          from Sales.SalesOrderHeader 
				       group by ShipMethodID)

   select ShipMethod.Name, POSales.POTotal, Sosales.SOTotal
   from Purchasing.ShipMethod
      left outer join POSales on ShipMethod.ShipMethodID = POSales.ShipMethodID    -- reference to cte1
      left outer join SOSales on ShipMethod.ShipMethodID = SOSales.ShipMethodID     -- reference to cte2

And while I don't use this one very often, you can do...


;with TempCTE1 as (select * from TableA where somecondition),
TempCTE2 as (select * from TempCTE1 where someadditionalcondition),
tempCTE3 as (select * from TempCTE2 where yetanothercondition)

select * from TempCTE3 join (some other table) etc etc