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.
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] ) ]
The statement to use the CTE is:
Note: The query using the CTE must be the first query appearing after the CTE.
WITH PriceList_CTE( ProductName, ListPrice, DiscountPrice) AS
SELECT [Name] AS ProductName, ListPrice, ListPrice * .95
SELECT ProductName, ListPrice, DiscountPrice
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.
WITH CTE_name ( column_name [,...n] )
CTE_query_definition –- Anchor member
CTE_query_definition –- Recursive member