Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 6, 2005

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

By Don Schlichting

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 ListPrice, (ListPrice * .95) AS SellPrice
  FROM Production.Product

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 ;
WITH DirectReports(LoginID, ManagerID, EmployeeID) AS 
    SELECT LoginID, ManagerID, EmployeeID
    FROM HumanResources.Employee
    SELECT e.LoginID, e.ManagerID, e.EmployeeID
	FROM HumanResources.Employee e
    INNER JOIN DirectReports d
    ON e.ManagerID = d.EmployeeID 
FROM DirectReports ;

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

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


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;

FROM DirectReports;


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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM