Common Table Expressions (CTEs)

Common Table Expressions (CTE) were first introduced back in SQL Server 2005. They provide a means to refer to a temporary result set by name, and thus utilize it as a table (albeit both temporary and virtual in nature). Perhaps the coolest thing about them is that you define them before actually using them, so you can avoid separate physical steps storing and re-referencing the table (as you would do with a temporary table — or even a table variable). This can have very favorable performance impacts since SQL Server can plan the work between the CTE and the queries that utilize it as part of one logical operation rather than as a series of separate activities. In their simplest form, CTEs are similar to views created on the fly, but a CTE can also enable other things that you can’t really do with a view (for example, see the following section on recursive queries).

The basic syntax for a CTE utilizes the WITH keyword followed by a name and definition:

WITH <CTE name> [( <column name> [,...n])]

AS

( <query returning tabular data> )

<statement that will make use of the CTE>

After the CTE is defined, you can refer to it by name just as if it were a table.

Note that while a CTE can nest, and a CTE can refer to a parent CTE, you cannot have completely inde-

pendent CTEs at the same time, nor can you reference forward in your nested CTEs. Indeed, whatever

statement is going to use the CTE must immediately follow the CTE declaration.

So, as an example of CTE use, we could replace part of our earlier derived table with a CTE reference:

USE AdventureWorks2008;

WITH pumps (BusinessEntityID)

AS

(

SELECT sc.PersonID AS BusinessEntityID

FROM Sales.Customer sc

JOIN Sales.SalesOrderHeader AS soh

ON sc.CustomerID = soh.CustomerID

JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderID = sod.SalesOrderID

JOIN Production.Product AS p

ON sod.ProductID = p.ProductID

WHERE p.Name = ‘Minipump’

)

SELECT DISTINCT pp.FirstName, pp.LastName

FROM Person.Person AS pp

JOIN pumps

ON pp.BusinessEntityID = pumps.BusinessEntityID

JOIN ( SELECT sc.PersonID AS BusinessEntityID

FROM Sales.Customer sc

JOIN Sales.SalesOrderHeader AS soh

ON sc.CustomerID = soh.CustomerID

JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderID = sod.SalesOrderID

JOIN Production.Product AS p

ON sod.ProductID = p.ProductID

WHERE p.Name = ‘AWC Logo Cap’) caps

ON pp.BusinessEntityID = caps.BusinessEntityID;

So here pumps is a temporary table with column name BusinessEntityID

Notice that I was able to cut the first derived table out entirely and replace it with the CTE reference. I

cannot, however, also replace the caps derived table, as I can only make one CTE reference at a time. I

can replace pumps, or I can replace caps, but not both.

It’s worth noting that certain constructs cannot be used within a CTE. These include:

❑ COMPUTER and COMPUTE BY

❑ ORDER BY

❑ INTO

❑ The FOR XML, FOR BROWSE, and OPTION query clauses