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