Common Table Expressions, despite their name, are not tables. Think of them as a temporary view that you can use to simplify a query. The 'temporary view' lasts for just one statement.
The following two queries are equivalent (and have the same execution plan)
Based off AdventureWorks
Without a CTE
SELECT SalesOrderNumber, PurchaseOrderNumber, TotalPerOrder FROM Sales.SalesOrderHeader soh INNER JOIN
(SELECT COUNT (*) TotalPerOrder, SalesOrderID FROM Sales.SalesOrderDetail sd GROUP BY sd.SalesOrderID) Sub
ON soh.SalesOrderID = sub.SalesOrderID
With a CTE
;WITH Sub (totalPerOrder, SalesOrderID) AS (
SELECT COUNT (*) TotalPerOrder, SalesOrderID FROM Sales.SalesOrderDetail sd GROUP BY sd.SalesOrderID
SELECT SalesOrderNumber, PurchaseOrderNumber, TotalPerOrder
FROM Sales.SalesOrderHeader soh INNER JOIN Sub ON soh.
What I did was to take the subquery in the first, and turn it into a CTE in the second.
All that's happened when the outer query executes, is that the definition of the CTE is placed into the query, replacing reference to the CTE, essentially becomming an inline subquery
There's no temp table of any form involved, hence no concerns regarding indexing or table scans.
Run those 2 in adventureworks with the execution plan on, and you'll see how they run.
I will be happy if you and jeff will have a
sharp eye on my post.
So I needn't have replied?
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass