Home » Beginner »Intermediate »sql server »sqlserverpedia-syndication »SSC »Tips »TSQL » Currently Reading:

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

Currently there are "11 comments" on this Article:

  1. Anonymous says:

    Twitter Trackbacks…

  2. Ryan Adams says:

    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 says:

    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. 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. Oh and you get +1000 street cred for including a Wil Wheaton recursion reference

  6. Jen McCown says:

    Wow, thanks Jorge!! You just made my day…

    And I accept the street cred :)

  7. [...] There’s no way to ferret out this information with a basic SELECT-FROM-WHERE query. You can’t say “WHERE Row_Number() = 1″ (because windowed functions can only appear in the SELECT or ORDER BY), or “WHERE RankNum = 1″ (because column level aliases can only appear in the ORDER BY clause).  We have to make the SQL engine pretend that this rowset is a persisted table.  While, yes, you could load this data into a temporary table or table variable, we know (in our particular circumstance) that we won’t need to access that data again. Why not use a CTE? Note: For an intro to CTEs, see N Things Worth Knowing About CTEs. [...]

  8. Bob Pusateri says:

    Thanks so much for contributing this post to T-SQL Tuesday!

  9. [...] McCown (blog | @JenniferMcCown) – Jen has offered up a previous post with a good list of N things to know about CTEs. She even gives an example of what happens when you [...]

  10. Chuck Rummel says:

    Love the recursive Wil Wheaton pic!

  11. [...] decent T-SQL, it could always be better. I’ve enjoyed the preview to this session in the many blog postings that Jen McCown (Blog | @MidnightDBA) has had over the past few [...]

Comment on this Article:







MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/