July 11, 2008 at 6:02 am
Can any one tell me what is Common Table Expression in Sql Server
Thanks in Advance
RC
July 11, 2008 at 6:18 am
Hi Ramesh,
Read BOL for the exact info.
A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.
A recursive CTE consists of three elements:
1. Invocation of the routine.
The first invocation of the recursive CTE consists of one or more CTE_query_definitions joined by UNION ALL, UNION, EXCEPT, or INTERSECT operators. Because these query definitions form the base result set of the CTE structure, they are referred to as anchor members.
CTE_query_definitions are considered anchor members unless they reference the CTE itself. All anchor-member query definitions must be positioned before the first recursive member definition, and a UNION ALL operator must be used to join the last anchor member with the first recursive member.
2. Recursive invocation of the routine.
The recursive invocation includes one or more CTE_query_definitions joined by UNION ALL operators that reference the CTE itself. These query definitions are referred to as recursive members.
3. Termination check.
July 11, 2008 at 6:21 am
Example of recursive common table expression (CTE) to display multiple levels of recursion is given below.
USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
GO
July 11, 2008 at 7:44 pm
Skipping all the lectures about the extreme RBAR of recursive CTE's...
A CTE is the same thing as a Subquery in the FROM clause (Derived table) except that it may be reference in the same query more than once.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply