I have heard that CTEs offer sometimes phenomenal performance gains. Is that true?
>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) ,
> 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...
