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
)
Labels: Common Table Expressions, SQL Server
posted by Sheron @ Friday, August 03, 2007
0 Comments
