Common Table Expressions (CTE) on SQL 2005 - Page 2

Derived Tables

A SQL 2000 method somewhat similar to a CTE is a Derived Table. These are temporary result sets created by a query statement. However, they cannot be referenced by name, and may be used only once. In complex statements, they may not be as readable as Common Table Expressions. The CTE rewritten as a Derived Table would be:

SELECT *
FROM (
  SELECT ListPrice, (ListPrice * .95) AS SellPrice
  FROM Production.Product
  )
MyDerivedTable

Common Table Expressions will not displace Derived Tables however. There will still be many instances when Derived Tables will be a better fit.  However, in some complex statements, Common Tables Expression may be clearer to understand.

Recursive Queries

One of the most powerful features of Common Table Expressions is their ability to be self-referenced by name, allowing for a recursive query. A Recursive Query is a query that calls itself. Below is a modified example of BOL recursive query. The purpose of the statement is to display each employee at Adventure Works, and who their manager is. The Employee table is self joined, where the ManagerID joins back on the same table to the EmployeeID.

The statement first looks for all top-level managers, the records where the ManagerID is null. In this table, there is only one. Next, we will self-reference this CTE by using its name "DirectReports" joined back to our top-level managers.

USE AdventureWorks ;
GO
WITH DirectReports(LoginID, ManagerID, EmployeeID) AS 
(
    SELECT LoginID, ManagerID, EmployeeID
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.LoginID, e.ManagerID, e.EmployeeID
	FROM HumanResources.Employee e
    INNER JOIN DirectReports d
    ON e.ManagerID = d.EmployeeID 
)
SELECT * 
FROM DirectReports ;
GO

Running this statement produces the following result set:

The first result line shows Ken, as the top-level manager, reporting to no one. Beneath him reports David, Terri, Peter, Jean, Laura, James and Brian. At Stephen, we see a change, with him reporting to Brian.

The Recursive Common Table Expression introduces some additional syntax. The statement now has two definition components. The first is called the Anchor Member:

SELECT LoginID, ManagerID, EmployeeID
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL

This statement must be followed by a UNION ALL. The purpose of the UNION ALL is to tie the results of two statements into one result set.

The next definition is called the recursive member. It calls back to itself using the CTE name; INNER JOIN DirectReports d

MAXRECURSION

The key word MAXRECURSION can be used as a query hint to stop a statement after a defined number of loops. This can stop a CTE from going into an infinite loop on a poorly coded statement. To use it in our above example, include it on the last line;

SELECT * 
FROM DirectReports;
OPTION (MAXRECURSION 3);

Conclusion

New in SQL 2005, Common Table Expressions will be worth exploring for anyone doing TSQL work. Their use in Recursive Queries alone warrants them worthy of consideration. Combined with their straightforward syntax, expect to see them used frequently in complex statements.

» See All Articles by Columnist Don Schlichting