N Things Worth Knowing About CTEs

Edit: This post was originally published on August 26, 2010.

If you haven’t messed with them yet, you should know that CTEs (Common Table Expressions) – new in SQL Server 2005 – are actualy pretty #awesomesauce. A CTE is, in essence, a temporary view attached to your SELECT statement.  They’re good for a number of uses, not the least of which is separating out some processing logic (like aggregation) for better understandability, or to avoid the use of temporary tables in a stored procedure.

Here’s a very simple example, using the AdventureWorks database:

WITH Top50 ( EmployeeID, FirstName, LastName, JobTitle )
AS ( SELECT TOP 50
EmployeeID ,
FirstName ,
LastName ,
JobTitle
FROM HumanResources.vEmployee
ORDER BY EmployeeID
)
SELECT EmployeeID ,
FirstName ,
LastName ,
JobTitle
FROM Top50
WHERE LastName < 'N'
ORDER BY LastName, FirstName

“WITH Top50” gives your CTE a name; the parentheses define the list of columns available. AS, of course, defines what data will be in the CTE…and then the SELECT in line 10 actually pulls data from the CTE.

A few important notes:

  • CTEs aren’t just for SELECT statements. They can be used with SELECT, INSERT, UPDATE, DELETE, MERGE, within SPs and triggers and functions, AND in CREATE VIEW statements.
  • This is one of the very few T-SQL statements that has a semicolon requirement: a statement preceding the CTE must end with a semicolon. For example:

SELECT 'We are starting!' -- AAAAH! No semicolon!!
WITH Top50 ( EmployeeID, FirstName, LastName, JobTitle )
AS ( SELECT TOP 50
EmployeeID ,
FirstName ,
LastName ,
JobTitle
FROM HumanResources.vEmployee
ORDER BY EmployeeID
)
SELECT EmployeeID ,
FirstName ,
LastName ,
JobTitle
FROM Top50
WHERE LastName < 'N'
ORDER BY LastName, FirstName

This will spit out the error “Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ‘Top50’.” Slap a semicolon on that “SELECT 'We are starting!'“.

Two of my Favorite Things!
Recursion and Wil Wheaton
  • One of the megacool uses of CTEs is recursion. BOL gives a great outline and examples of usin a recursive CTE to pull back a hierarchical list of employees (the standard JOIN dbo.Employee M ON E.managerID = M.employeeID scenario).
  • You can also define MULTIPLE CTEs in a single statement, with the format:
    WITH <cteName> (<columns>) AS (<select statement>),
      <cteName (<columns>) AS (<select statement>)
    <SELECT statement>
    Here is a very academic example (no one would need THIS resultset!), just to show you how it looks:

 
WITH Top50 ( EmployeeID, FirstName, LastName, JobTitle )
AS ( SELECT TOP 50
  EmployeeID ,
  FirstName ,
  LastName ,
  JobTitle
FROM HumanResources.vEmployee
ORDER BY EmployeeID
),
Bottom50 ( FirstName, LastName, JobTitle ) -- second CTE!
AS ( SELECT TOP 50
  FirstName ,
  LastName ,
  JobTitle
FROM HumanResources.vEmployee
ORDER BY EmployeeID DESC
)
SELECT Top50.EmployeeID ,
  Top50.FirstName ,
  Top50.LastName ,
  Top50.JobTitle ,
  Bottom50.FirstName ,
  Bottom50.LastName
FROM Top50
INNER JOIN Bottom50 ON Top50.Title = Bottom50.Title
WHERE LastName < 'N'
ORDER BY LastName, FirstName

And there you go…roughly N things you should know about CTEs. For your enjoyment, here is some further reading from BOL:

Happy days,

Jen McCown

http://www.MidnightDBA.com/Jen

11 thoughts on “N Things Worth Knowing About CTEs

  1. Pingback: Anonymous

  2. Ryan Adams

    Good post. Anytime I see code with a temp table I replace it with a CTE. Also many times we all wish T-SQL had an array and cursors are what we have used (if and only if necessary). CTEs can help avoid those cursors in many cases.

  3. Jen McCown Post author

    Agreed, and honestly I need to get in the habit of using CTEs more often. I have 5+ years of using temp tables when need be…

  4. Jorge Segarra

    Dude, this is probably the clearest explanation and demo of CTE’s I’ve come across and it FINALLY clicked with me. You are #awesomesauce personified!

  5. Pingback: SQL Awesomesauce » Blog Archive » T-SQL Tuesday #016: Aggregates and Windowing Functions and Ranking! Yum!

  6. Pingback: T-SQL Tuesday #18 Wrapup | Bob Pusateri - The Outer Join

  7. Pingback: My 24HOP Schedule for September 16, 2010Matt Velic | Matt Velic

Comments are closed.