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!'“.
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:
- WITH common_table_expression
- Using Common Table Expressions
- Recursive Queries Using Common Table Expressions
Happy days,
Jen McCown
