Common Table Expressions (CTEs) | Microsoft SQL Server 2005: Changing the Paradigm (SQL Server 2005 Public Beta Edition)

A new T-SQL enhancement in SQL Server 2005 that will have a dramatic effect on future queries is common table expressions (CTEs), which are defined in SQL-99. CTEs can be thought of as a type of derived table, but unlike with derived tables, the result set of CTEs can be defined once and used multiple times in the defining query. With this capability to define once and use it several times, CTEs can even reference themselves, making CTEs superior to other methods for defining hierarchies with a SQL Server query.

Here is the simplistic CTE syntax:

WITH <cte_alias>(<column_aliases>) AS (       <cte_query> ) SELECT * FROM <cte_alias>;

SQL Server 2005 supports two different types of CTEs: non-recursive and recursive. CTEs that do not have self-references are non-recursive, whereas recursive CTEs have selfreferences. Here is an example of simple non-recursive and recursive CTEs:

USE AdventureWorks; GO --Non-recursive CTE WITH NonRecCTE (cnt) AS (     SELECT COUNT(EmployeeID) AS cnt     FROM HumanResources.Employee     GROUP BY ManagerID ) SELECT AVG(cnt) AS AvgDirectReports FROM NonRecCTE; --Recursive CTE WITH RecCTE(ManagerID, EmployeeID, EmployeeLevel) AS (     SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel        FROM HumanResources.Employee        WHERE ManagerID IS NULL     UNION ALL     SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1        FROM HumanResources.Employee e        INNER JOIN RecCTE r        ON e.ManagerID = r.EmployeeID ) SELECT EmployeeID, ManagerID, EmployeeLevel FROM RecCTE; GO

In the non-recursive CTE query in this example, the inner query groups the employees by ManagerID and returns the count. The outer query in this non-recursive CTE then performs an average on this count column and returns a number indicating the average number of direct reports in the Employee table.

A recursive CTE always contains at least two queries combined using a UNION ALL clause. The first query, called the anchor member, is the starting point (usually the first row in the result set). In the second query, the called recursive member follows the UNION ALL clause, and in this query the CTE refers to itself. The preceding recursive CTE query returns the EmployeeID, the ManagerID, and the level of the employee in the hierarchy.

The most common problem with recursion is infinite loops. SQL Server solves this problem by defining a serverwide recursion level limit setting, called MAXRECURSION, which defaults to 100. You can also specify the MAXRECURSION hint in your outer query to limit the recursion level for that query.

A CTE can refer to another CTE, as illustrated in the following query:

WITH YearlyOrderAmtCTE(OrderYear, TotalAmount) AS (      SELECT YEAR(OrderDate), SUM(OrderQty*UnitPrice)      FROM Sales.SalesOrderHeader AS H JOIN Sales.SalesOrderDetail AS D           ON H.SalesOrderID = D.SalesOrderID      GROUP BY YEAR(OrderDate) ), SalesTrendCTE(OrderYear, Amount, AmtYearBefore, AmtDifference, DiffPerc) AS (  SELECT thisYear.OrderYear, thisYear.TotalAmount,         lastYear.TotalAmount,         thisYear.TotalAmount - lastYear.TotalAmount,         (thisYear.TotalAmount/lastYear.TotalAmount - 1) * 100  FROM YearlyOrderAmtCTE AS thisYear       LEFT OUTER JOIN YearlyOrderAmtCTE AS lastYear       ON thisYear.OrderYear = lastYear.OrderYear + 1 ) SELECT * FROM SalesTrendCTE; GO

This query defines two non-recursive CTEs. The first CTE, called YearlyOrderAmtCTE, groups the total sales by year. This CTE is then used in the second CTE, called SalesTrendCTE, and the outer or main query selects all the rows from SalesTrendCTE. The preceding query returns the following results:

OrderYear Amount          AmtYearBefore  AmtDifference  DiffPerc --------- --------------- -------------- -------------- --------- 2001      11336135.376    NULL           NULL           NULL 2002      30859192.305    11336135.376   19523056.929   172.21 2003      42308575.2263   30859192.305   11449382.9213  37.10 2004      25869986.4061   42308575.2263  -16438588.8202 -38.86

Note

When a CTE is used in a statement that is part of a batch, the preceding statement in the batch must be followed by a semicolon.

Neither recursive nor non-recursive CTEs can include the following clauses in the defining, or inner, query for the CTE:

COMPUTE or COMPUTE BY
ORDER BY (except when a TOP clause is specified)
INTO
OPTION clause with query hints
FOR XML
FOR BROWSE

Recursive CTEs have the additional following restrictions:

The FROM clause of the recursive member must refer only once to the CTE expression_name.
The recursive member CTE_query_definitions does not allow SELECT DISTINCT, GROUP BY, HAVING, scalar aggregation, TOP, LEFT or RIGHT OUTER JOIN (INNER JOIN is allowed), functions with input or output parameters, or subqueries.

Let's look at the power of CTEs, using a simple example. In this example, a report is created to show each manager, his or her direct reports, and the employees under each manager's direct reportskind of an organization chart:

WITH OrgChart  (FirstName, LastName, Title, ManagerID, EmployeeID, EmpLevel, SortValue) AS (     SELECT ct.FirstName, ct.LastName, emp.Title, emp.ManagerID,            emp.EmployeeID, 0 AS EmpLevel,          CAST(emp.EmployeeID AS VARBINARY(900))     FROM HumanResources.Employee emp INNER JOIN Person.Contact ct             ON ct.ContactID = emp.ContactID     WHERE emp.ManagerID IS NULL     UNION ALL     SELECT ct.FirstName, ct.LastName, emp.Title, emp.ManagerID,            emp.EmployeeID, EmpLevel + 1,            CAST(SortValue + CAST(emp.EmployeeID AS BINARY(4))                  AS VARBINARY(900))     FROM HumanResources.Employee emp JOIN Person.Contact AS ct             ON ct.ContactID = emp.ContactID          JOIN OrgChart org             ON emp.ManagerID = org.EmployeeID ) SELECT    EmpLevel, REPLICATE(' ', EmpLevel) + FirstName + ' ' + LastName    AS 'Employee Name', Title FROM OrgChart ORDER BY SortValue; GO

The anchor member in this recursive CTE query finds out about an employee who does not have a manager (ManagerID IS NULL). Then the recursive member finds out the topmost manager's direct reports, and then in recursion it finds out about employees reporting to direct reports. The outer SELECT statement uses the REPLICATE function and EmpLevel and SortValue columns to generate a formatted organization chart of employees. The query produces the following results (some rows have been omitted for brevity):

EmpLevel  Employee Name                   Title --------  ------------------------------  ---------------------------- 0         Ken Sánchez                     Chief Executive Officer 1           David Bradley                 Marketing Manager 2               Kevin Brown               Marketing Assistant 2               Sariya Harnpadoungsataya  Marketing Specialist 2               Mary Gibson               Marketing Specialist 2               Jill Williams             Marketing Specialist 2               Terry Eminhizer           Marketing Specialist 2               Wanida Benshoof           Marketing Assistant 2               John Wood                 Marketing Specialist 2               Mary Dempsey              Marketing Assistant 1           Terri Duffy                   Vice President of Engineering 2               Roberto Tamburello        Engineering Manager

Tip

Notice the use of the semicolon (;) as the statement separator in the scripts throughout this chapter. SQL Server 2005 recommends using the semicolon as a statement terminator. In addition, the semicolon is required at the end of the preceding statement when the CTE or Service Broker statement (for example, SEND or RECEIVE) is not the first statement in a batch or module.