Jerry Nixon on Windows: Common Table Expression with the SQL 2005 "WITH" statement

Jerry Nixon on Windows

Friday, March 14, 2008

Common Table Expression with the SQL 2005 "WITH" statement

A common table expression has a lot of limits. Let's just start with that. But there are some cases where it is one of the only, or at least most elegant, solutions. Here's how it is used (against Northwind data). I wrote this back for the 2005 PASS camp. It's taken two years to put it on the web - and now SQL 2008 is about to be released. Shameful.

/* 
derived table vs common table expression
*/

Select * From
(Select
FirstName
,LastName
From Employees) As [x];

-- common table expression

With [x] As
(Select
FirstName
,LastName
From Employees)
Select * From [x];

-- reuse of cte

With [x] As
(Select
FirstName
,LastName
From Employees)
Select * From [x]
Union All
Select * From [x]

-- a cte can reference itself

With [x] As
(Select
[e].EmployeeId
,[e].ReportsTo
,[e].Title
,0 As [Level]
From
Employees As [e]
Where
[e].ReportsTo is null
Union All
Select
[e].EmployeeId
,[e].ReportsTo
,[e].Title
,[x].Level + 1
From
Employees As [e] JOIN [x] ON [e].ReportsTo = [x].EmployeeId)
Select * From [x]
Order By ReportsTo

-- can cause infinate loop / limit recursion

OPTION (MAXRECURSION 2);

-- more than one common table

With [x] As
(Select
CustomerId
,ContactName
From Customers)
,[y] As
(Select
CustomerId
,Max(OrderDate) As [LastOrder]
From Orders
Group By CustomerId)
Select TOP (10)
[x].ContactName
,[y].LastOrder
From
[x] JOIN [y] ON [x].CustomerId = [y].CustomerId

-- named columns

With [x] (LastName, FirstName, Title) As
(Select
LastName As [Name]
,FirstName As [Name]
,Title
From Employees)
Select
[x].*
From
[x]

-- cte to access calculations

With [x] As
(
Select
Row_Number() OVER (ORDER BY Title) As [RowNumber]
,Title
From
Employees
)
Select
[x].[RowNumber]
,[x].Title
,1 As [Page]
From
[x]
Where
[RowNumber] between 1 and 3
Union
Select
[x].[RowNumber]
,[x].Title
,2 As [Page]
From
[x]
Where
[RowNumber] between 3 and 5