More About Common Table Expressions in SQL Server 2005
(Page 1 of 5 )
This is the second part of a tutorial focusing on common table expressions in SQL Server 2005. In the previous article, I explained non-recursive CTEs. Therefore, in this article, I shall mainly focus on recursive CTEs in SQL Server 2005.
If you are new to CTEs, I strongly suggest you go through my first article on the topic here.
All the examples in this article were tested using SQL Server 2005 Enterprise Edition. Please note that I didn’t really test the examples on any of other versions/editions or similar suites of Microsoft products (even though I strongly believe that the examples will work on all SQL Server 2005 editions).
You also need to consider that the examples are not at all optimized for performance. They may not be used for a real production environment. I just wanted to introduce the concept of recursive CTEs through this article without any discussion of performance tuning (which is beyond the scope of this article).
Coming to the topic, what exactly is a “recursive CTE”? A CTE which includes references to itself within its own body is called a recursive CTE. Recursion is generally specified using any SET operator (like UNION ALL, UNION, INTERSECT, or EXCEPT) within the CTE. The second part of the query (which is after the SET operator) must refer to the CTE itself, using it as a key to the next level of recursion.
Some clauses like DISTINCT, GROUP BY, HAVING, TOP, LEFT, RIGHT, OUTER JOIN, (INNER JOIN is allowed) along with scalar aggregations, sub queries and so forth are not allowed within a CTE recursive member definition.
A recursive common table expression: an example
Before talking too much, let us start with a simple example.
SELECT
EmployeeID
FROM HumanResources.Employee
WHERE ManagerID = 140
The above example (even though it is not a CTE) simply displays all EmployeeIDs, who are under manager 140 (the manager id or employee id of the manager). But the employees under manager 140 may have their own subordinates. So within the above list we are covering only the first level of subordinates under manager 140, and not really listing all the levels of subordinates under him or her.
Now let us work with our new CTE example:
WITH EmpCTE
AS
(
SELECT
EmployeeID, ManagerID
FROM HumanResources.Employee
WHERE ManagerID = 140
UNION ALL
SELECT
b.EmployeeID, b.ManagerID
FROM HumanResources.Employee b
JOIN EmpCTE AS a ON a.EmployeeID = b.ManagerID
)
SELECT EmployeeID,ManagerID
FROM EmpCTE
The sample output for the above CTE would be something like the following:
EmployeeID ManagerID
---------- ---------
30 140
71 140
103 140
139 140
59 139
94 139
130 139
.
.
.
82 30
154 30
191 30
I shall explain the above script in the next section.
Next: A recursive common table expression: explanation >>
More MS SQL Server Articles
More By Jagadish Chaterjee
