Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

N Things Worth Knowing About CTEs

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

Comments

Posted by Anonymous on 28 August 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, N Things Worth Knowing About CTEs - SQL Awesomesauce         [sqlservercentral.com]        on Topsy.com

Posted by Charles Kincaid on 29 August 2010

One of our products has the ability to send a work item to a manager for review before completion.  Well a work item may be reviewed by a manager, sent back for rework, reviewed again, reworked some more, another review, a bit more rework, passed the review this time, and finally sent out.

Same thing for hold status.  It can be placed on hold, taken off of hold, back on hold again, etc.  The customer wanted, for every completed item, a list that show the last of reviews, hold status, re-assigned to a different employee, and eight other such things.  The developemnt team was locking the server for minutes at a time running this query.  And that is just in the test environment.

CTEs to the rescue.  One for each of the action columns based on item ID.

Posted by Mike Voissem on 30 August 2010

I have found CTE's to be especially useful for when I've had to do comparative aggregations to previous years data, and even better yet when I compare the previous row to the current rows data.

Posted by self.soul.friend on 1 September 2010

Just an aside: I usually just START my CTE with a semicolon so I don't have to worry about ending a previous statement with one.  While a CTE without a semicolon will work if it's the first thing executed, it will always work if it starts with ";WITH" no matter what comes before it.

Posted by Jen McCown on 7 September 2010

self.soul.friend,

That's a good strategy, if (like me) you haven't yet trained yourself into ending all statements with a semicolon.

Leave a Comment

Please register or log in to leave a comment.