http://www.sqlservercentral.com/blogs/sql_awesomesauce/2010/08/26/n-things-worth-knowing-about-ctes/

Printed 2014/12/21 09:46AM

N Things Worth Knowing About CTEs

By Jen McCown, 2010/08/26

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:

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

 
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


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.