SQL 2005 Common Table Expressions

Common Table Expressions is a newly introduced feature in SQL Server 2005. It is similar to a temp table or a view, but its scope is limited to a single SELECT statement.

Syntax

A CTE is made up of

1. CTE name

2. An optional column list

3. A query defining the CTE

The basic syntax structure for a CTE is:

WITH CTE_name [ ( column_name [,...n] ) ]
AS
(
CTE_query_definition
)

The statement to use the CTE is:

SELECT
FROM CTE_name

Note: The query using the CTE must be the first query appearing after the CTE.

Example

WITH PriceList_CTE( ProductName, ListPrice, DiscountPrice) AS
(
SELECT [Name] AS ProductName, ListPrice, ListPrice * .95
FROM Production.Product
)

SELECT ProductName, ListPrice, DiscountPrice
FROM PriceList_CTE

Recursive CTEs

Used for easily traversing heirarchical data structures. Eg: Displaying employees in an organizational chart.

A recursive CTE consists of three elements:

1. Initial sub-query

The seed value.

2. Recursive sub-query

The recursive portion contains a reference to the rows added during the previous iteration.

3. Termination check

Recursion stops automatically whenever an iteration generates no new rows. The final resultset is the union of all result sets generated by the anchor and recursive members.

Syntax

WITH CTE_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member

UNION ALL

CTE_query_definition –- Recursive member
)