This is a continuation of Message #1593233 (as part of a very good thread discussion) on Common Table Expressions vs temp tables.

First, let's take an extremely simple example...

 ;with TestCTE as (select * from Purchasing.PurchaseOrderHeader where OrderDate between '1-1-2008' and '3-31-2008' )
  select * from TestCTE where ShipMethodID = 1

At the surface one might initially think (and I first thought this when I started working with CTEs) that somewhere, for a brief period of time, there's some "temp something" called TestCTE...either in server memory, or in temp db....that the rows reflecting the orders for the first quarter here are stored "somewhere"

Well, that's actually not the case. If I were to simply execute this...

 ;with TestCTE as (select * from Purchasing.PurchaseOrderHeader where OrderDate between '1-1-2008' and '3-31-2008' )

....I'd actually get an error message! Here's why....a common table expression is (in this situation) "syntax sugar" for a derived table subquery...with the subquery defined one line of code before, for a bit of clarity. If we define a CTE but don't use it, SQL Server views it as incomplete...it needs the subsequent line of code that actually defines what we will do with the CTE. So when we say...

 ;with TestCTE as (select * from Purchasing.PurchaseOrderHeader where OrderDate between '1-1-2008' and '3-31-2008' )
  select * from TestCTE where ShipMethodID = 1

It's (for all intents and purposes) the same as saying the following, which is a derived table subquery

   select * from  (select * from Purchasing.PurchaseOrderHeader where OrderDate between '1-1-2008' and '3-31-2008' ) TempList
     where ShipMethodID = 1

So....it's not a temp table with a short life....it's basically a derived table subquery, placed above the line of code that uses it. Some developers find the syntax a bit easier, while older SQL developers (prior to SQL 2005) prefer the older style derived table syntax.

Now...the example above didn't need a CTE...clearly that could have been written as one straight query without any subquery. But here's an example where either a CTE or subquery IS needed....if I want to query against multiple 1-many relationships. In AdventureWorks, I might want to show all ship Methods, the sum of Purchase Order $$$ by ship method, and the sum of Sales Order $$$ by Ship Method. This can't be done in one query....we need to separate the 2 aggregations in separate subqueries. I can do this:

;with POSales as 
        ( select ShipMethodID, sum(TotalDue)  as POTotal 
		        from Purchasing.PurchaseOrderHeader 
				       group by ShipMethodID) ,   -- note the 2 CTEs, separated by a comma
   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

The "old-style" way using derived table subqueries will produce the same execution plan, and "usually" run in about the same amount of time. So here, the code for the two CTEs are now "in-line", instead of defined above. But both examples are treated as one execution.

select ShipMethod.Name, POSales.POTotal, Sosales.SOTotal
  from Purchasing.ShipMethod
      left outer join  ( select ShipMethodID, sum(TotalDue)  as POTotal 
		        from Purchasing.PurchaseOrderHeader 
				       group by ShipMethodID)  POSales on ShipMethod.ShipMethodID = POSales.ShipMethodID

      left outer join ( Select ShipMethodID, sum(TotalDue) as SOTotal 
		          from Sales.SalesOrderHeader 
				       group by ShipMethodID) SOSales on ShipMethod.ShipMethodID = SOSales.ShipMethodID

So bottom line....CTEs in this situation are essentially the same as derived table subqueries.


Now, there's a second use of CTEs....to implement recursion....a more advanced different situation.

Hope this helps...