Common Table Expressions (CTE) in SQL Server 2005

Common Table Expressions, or CTE, are a new construct introduced in Microsoft SQL Server 2005 that offer a more readable form of the derived table that can be declared once and referenced multiple times in a query. Moreover, CTEs can be recursively defined, allowing a recursive entity to be enumerated without the need for recursive stored procedures. In this article we'll examine the benefits, uses, and syntax of both recursive and non-recursive CTEs. Read on to learn more!

A Simple Common Table Expression Example
Before we dive into the syntax or gritty details of CTEs, let's start by looking at a simple example. I think you'll agree that even without knowing the syntax of CTEs, they are pretty readable and straightforward (the hallmark of a well-designed programming language construct).

WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS (    SELECT       p.ProductName,       c.CategoryName,       p.UnitPrice    FROM Products p       INNER JOIN Categories c ON          c.CategoryID = p.CategoryID    WHERE p.UnitPrice > 10.0 ) SELECT * FROM ProductAndCategoryNamesOverTenDollars ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC

This query creates a CTE named ProductAndCategoryNamesOverTenDollars that returns the name, category name, and price of those products whose unit price exceeds $10.00. Once the CTE has been defined, it must then immediately be used in a query. The query treats the CTE as if were a view or table in the system, returning the three fields defined by the CTE (ProductName, CategoryName, andUnitPrice), ordered alphabetically by category, then by price, and then alphabetically by product name.

In short, a Common Table Expression allows us to define a temporary, view-like construct. We start by (optionally) specifying the columns it returns, then define the query. Following that, the CTE can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Common Table Expression Syntax
A Common Table Expression contains three core parts:

• The CTE name (this is what follows the WITH keyword)
• The column list (optional)
• The query (appears within parentheses after the AS keyword)

The query using the CTE must be the first query appearing after the CTE. That is, you cannot do the following:

WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS (    SELECT       p.ProductName,       c.CategoryName,       p.UnitPrice    FROM Products p       INNER JOIN Categories c ON          c.CategoryID = p.CategoryID    WHERE p.UnitPrice > 10.0 ) SELECT * FROM Products SELECT * FROM ProductAndCategoryNamesOverTenDollars ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC

The ProductAndCategoryNamesOverTenDollars CTE only applies to the first query following it. So when the second query is reached,ProductAndCategoryNamesOverTenDollars is undefined, resulting in an "Invalid object name 'ProductAndCategoryNamesOverTenDollars'" error message.

You can, however, define multiple CTEs after the WITH keyword by separating each CTE with a comma. For example, the following query uses two CTEs. The subsequent SELECT query then uses an INNER JOIN to match together the records from the two CTEs:

WITH CategoryAndNumberOfProducts (CategoryID, CategoryName, NumberOfProducts) AS (    SELECT       CategoryID,       CategoryName,       (SELECT COUNT(1) FROM Products p        WHERE p.CategoryID = c.CategoryID) as NumberOfProducts    FROM Categories c ), ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS (    SELECT       ProductID,       CategoryID,       ProductName,       UnitPrice    FROM Products p    WHERE UnitPrice > 10.0 ) SELECT c.CategoryName, c.NumberOfProducts,       p.ProductName, p.UnitPrice FROM ProductsOverTenDollars p    INNER JOIN CategoryAndNumberOfProducts c ON       p.CategoryID = c.CategoryID ORDER BY ProductName

Unlike a derived table, CTEs can be defined just once, yet appear multiple times in the subsequent query. To demonstrate this, consider the following example: the Northwind database's Employees table contains an optional ReportsTo column that, if specified, indicates the employee's manager. ReportsTo is a self-referencing foreign key, meaning that, if provided, it refers back to anotherEmployeeID in the Employees table. Imagine that we wanted to display a list of employees including how many other employees they directly managed. This could be done using a simple, CTE-free SELECT statement, but let's use a CTE for now (for reasons which will become clear soon):

WITH EmployeeSubordinatesReport (EmployeeID, LastName, FirstName, NumberOfSubordinates, ReportsTo) AS (    SELECT       EmployeeID,       LastName,       FirstName,       (SELECT COUNT(1) FROM Employees e2        WHERE e2.ReportsTo = e.EmployeeID) as NumberOfSubordinates,       ReportsTo    FROM Employees e ) SELECT LastName, FirstName, NumberOfSubordinates FROM EmployeeSubordinatesReport

This query will return the employees records, showing each employee's last and first name along with how many other employees they manage. As the figure below shows, only Andrew Fuller and Steven Buchanan are manager material.

Now, imagine that our boss (Andrew Fuller, perhaps) comes charging into our office and demands that the report also lists each employee's manager's name and number of subordinates (if the employee has a manager, that is - Mr. Fuller is all to quick to point out that he reports to no one). Adding such functionality is a snap with the CTE - just add it in a LEFT JOIN!

WITH EmployeeSubordinatesReport (EmployeeID, LastName, FirstName, NumberOfSubordinates, ReportsTo) AS (    SELECT       EmployeeID,       LastName,       FirstName,       (SELECT COUNT(1) FROM Employees e2        WHERE e2.ReportsTo = e.EmployeeID) as NumberOfSubordinates,       ReportsTo    FROM Employees e ) SELECT Employee.LastName, Employee.FirstName, Employee.NumberOfSubordinates,    Manager.LastName as ManagerLastName, Manager.FirstName as ManagerFirstName, Manager.NumberOfSubordinates as ManagerNumberOfSubordinates FROM EmployeeSubordinatesReport Employee    LEFT JOIN EmployeeSubordinatesReport Manager ON       Employee.ReportsTo = Manager.EmployeeID

With this additional LEFT JOIN, the employee's manager's results are returned; if there's no manager for the employee, NULLs are returned instead.