SQL Server 2005 adds a new feature called Common Table Expressions (CTE). The true power of CTEs emerges when you use them recursively to perform hierarchical queries on tree structured data. In fact, besides SQL-92 compliance, this was the main reason Microsoft built CTEs. A recursive CTE is constructed from a minimum of two queries, the first, or anchor member (AM), is a nonrecursive query, and the second, or recursive member (RM), is the recursive query. Within your CTE’s parentheses (after the AS clause), you define queries that are either independent or refer back to the same CTE. The AM and RM are separated by a UNION ALL statement. Anchor members and are invoked only once and are invoked repeatedly until the query returns no rows. Multiple AMs can be appended to each other using either a UNION or a UNION ALL operator, depending on whether you want to eliminate duplicates. (You must append recursive members using a UNION ALL operator.) Here is the syntax:
With SimpleRecursive( field names)
As
(
<Select Statement for the Anchor Member>
Union All
<Select Statement for the Recursive Member>
)
Select * From SimpleRecursive
To demonstrate this feature, I will create an example here. We create a table with employees and a self referencing field back to Employee_ID called ReportsTo (I call this a Domestic Key in lue of a Foreign Key). We are going to write a query that returns all the employees who report to Stephen (Employee_ID=2) and all the employees who report to Stephen’s subordinates:
--create a table with tree data
--Reportsto is a "domestic key" back to Employee_id
create table Employee_Tree (Employee_NM nvarchar(50), Employee_ID int Primary Key, ReportsTo int)
--insert some data, build a reporting tree
insert into employee_tree values('Richard', 1, 1)
insert into employee_tree values('Stephen', 2, 1)
insert into employee_tree values('Clemens', 3, 2)
insert into employee_tree values('Malek', 4, 2)
insert into employee_tree values('Goksin', 5, 4)
insert into employee_tree values('Kimberly', 6, 1)
insert into employee_tree values('Ramesh', 7, 5)
Our table looks like this:
|
Employee_NM |
Employee_ID |
ReportsTo |
|
Richard |
1 |
null |
|
Stephen |
2 |
1 |
|
Clemens |
3 |
2 |
|
Malek |
4 |
2 |
|
Goksin |
5 |
4 |
|
Kimberly |
6 |
1 |
|
Ramesh |
7 |
5 |
Now the recursive query to determine all the employees who will report to Stephen:
--Recursive Query
WITH SimpleRecurvice(Employee_NM, Employee_ID, ReportsTO)
AS
(SELECT Employee_NM, Employee_ID, ReportsTO
FROM Employee_Tree WHERE Employee_ID = 2
UNION ALL
SELECT p.Employee_NM, p.Employee_ID, p.ReportsTO
FROM Employee_Tree P INNER JOIN
SimpleRecurvice A ON A.Employee_ID = P.ReportsTO
)
SELECT Employee_NM FROM SimpleRecurvice
Employee_NM
--------------------------------------------------
Stephen
Clemens
Malek
Goksin
Ramesh
(5 row(s) affected)
This recursion starts where Employee_ID= 2 (the ANCHOR MEMBER or the first SELECT). It picks up that record and then, via the RECURSIVE MEMBER (the SELECT after the UNION ALL), picks up all of the records that report to Stephen and that record’s children (Goksin reports to Malek and Malek reports to Stephen). Each subsequent recursion tries to find more children that have as parents the employees found by the previous recursion. Eventually the recursion returns no results and that is what causes the recursion to stop (the reason why Kimberly is not returned).