Using common table expressions in SQL Server 2005 queries

by Arthur Fuller | Nov 20, 2006 8:00:00 AM

Tags: Databases, Microsoft SQL Server, Arthur Fuller, Microsoft SQL Server 2005, common table expression, SQL Server Newsletter

Takeaway: Common table expressions (CTEs), a powerful and slick new feature in SQL Server 2005, make your SQL more readable and easier to maintain. Arthur Fuller demonstrates how you can use a CTE more than once in a SQL Server statement.

New in SQL Server 2005 are common table expressions (CTEs). They are similar to aliases (as in SELECT T1.* FROM MyTable T1) but much more useful. In essence, a CTE is a temporary result set that exists only within the scope of the statement in which it occurs. You can create a CTE within a SELECT, INSERT, DELETE, UPDATE, or CREATE VIEW statement. CTEs resemble derived tables but with several advantages.

Advantages of CTEs

Unlike derived tables, CTEs can reference themselves. You can use a CTE in place of a view when you don't need to store the view. You can also reference the CTE multiple times within a single statement. Using a CTE, you can group your results by a derived column.

I have written previously about what I call atomic and molecular queries; atomic queries address one table, while molecular queries are built up from the atoms providing clarity and reusability. Using CTEs, you can do the same thing. You can isolate parts of the query into readable "chunks" and then build up a complex query from the chunks. The most significant and powerful thing you can do with a CTE is perform a recursive query.

Creating a CTE

A CTE is created by using the keyword WITH. The template is:

WITH CTE_name[ (column_name [,...n] ) ]

AS

( CTE_query_specification )

If the column names mentioned in the CTE definition are unique, then you don't need to name them. However, naming them also lets you give them new column names.

Here is an example that uses the AdventureWorks sample database included with SQL Server 2005. This database is highly normalized and, as a result, requires several joins to assemble the information concerning employees. Views simplify this, but also gather all the information regarding employees, and you may only require some of it.

The data regarding employees of AdventureWorks is distributed among several tables. This is further complicated by the fact that employees and managers are both stored in a single table (HumanResources.Employee), while their names (and other data) are stored in the Person.Contact table.

First, let's create a CTE that retrieves the employee name:

WITH cte_Employee
AS
(
      SELECT  e.EmployeeID, c.FirstName, c.MiddleName, c.LastName,
e.Title AS JobTitle, c.Phone, e.ManagerID
      FROM    HumanResources.Employee AS e 
INNER JOIN Person.Contact AS c ON c.ContactID = e.ContactID
)

Then, we can select one or more columns from the CTE as if it were a standard table or view.

Let's up the ante a little. We want the names of the employees and their managers, so we'll use the CTE twice, joining it to itself. Here is the complete query:

WITH cte_Employee
AS
(
      SELECT  e.EmployeeID, c.FirstName, c.MiddleName, c.LastName, 
              e.Title AS JobTitle, c.Phone, e.ManagerID
      FROM    HumanResources.Employee AS e 
      INNER JOIN Person.Contact AS c ON c.ContactID = e.ContactID
)
SELECT E.FirstName + ' ' E.LastName Employee, 
       M.FirstName + ' ' M.LastName Manager
FROM    cte_Employee AS E 
        LEFT OUTER JOIN cte_Employee AS M 
        ON E.ManagerID = M.EmployeeID

One proviso: You cannot create two CTEs within a single statement.

Summary

CTEs are a powerful and slick new feature in SQL Server 2005. They make your SQL Server more readable and easier to maintain, masking the complexity of tricky queries. As shown here, you can use a CTE more than once within a SQL Server statement.

In my next tip, I will show you how to use this capability to perform a recursive query, such as you might find in a bill-of-materials application.

Miss a tip?

Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.

Print/View all Posts Comments on this article

More than once? fastboxster | 11/21/06

Testing the example jwpierce1830 | 11/22/06

RE: Using common table expressions in SQL Server 2005 queries dcolumbich@... | 07/14/08