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 #
1593237It'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
( select ShipMethodID, sum(TotalDue) as POTotal
from Purchasing.PurchaseOrderHeader
group by ShipMethodID) ,
SOSales as
(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
left outer join SOSales on ShipMethod.ShipMethodID = SOSales.ShipMethodID
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