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
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:
Recursive CTEs have the additional following restrictions:
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
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.