Using Common Table Expressions for recursive queries in SQL Server

The Common Table Expression (CTE) was introduced in SQL Server 2005 and can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

A CTE can be used in many of the same ways you use a derived table. CTEs can also contain references to themselves. This allows the developer to write complex queries more simply. CTEs can also be used in place of views. The use of CTEs provides two main advantages. One is that queries with derived table definitions become more simple and readable. While traditional T-SQL constructs that are used to work with derived tables normally requires a separate definition for the derived data such as a temporary table or a table-valued function, using CTEs make it easier to see the definition of the derived table with the code that uses it. The other thing is that CTEs significantly reduces the amount of code required for a query that traverses recursive hierarchies.

To understand what a CTE is all about, let's take a look at the syntax to create it in SQL Server 2005 and higher.

Syntax
In it's general form a recursive CTE has the following syntax:

WITH cte_alias (column_aliases)  
AS  
(
cte_query_definition   --initialization query
UNION ALL
cte_query_definition2 --recursive execution query
)  
SELECT * FROM cte_alias  

You provide the CTE with an alias and an optional list of aliases for its result columns following the keyword WITH, which usually defines the derived table based on the query definition; you write the body of the CTE; and then you refer to it from the outer query.

To put this in an easy-to-understand form, let’s use a simple example that employs recursion. If you look at the Employees table in the Northwind database you'll see that a particular employee reports to another employee via the "ReportsTo" column. The Employees table is designed in such a way that the ReportsTo column is a foreign key field that refers to the primary key field EmployeeID in the same table. Thus, a sample query using CTE will look something like this.


WITH Managers AS
(
--initialization query - gets managers
SELECT EmployeeID, LastName, ReportsTo  
FROM Employees
WHERE ReportsTo IS NULL
UNION ALL
--recursive execution query
SELECT e.employeeID,e.LastName, e.ReportsTo
FROM Employees e INNER JOIN Managers m  
ON e.ReportsTo = m.employeeID
)
SELECT * FROM Managers

Executing this CTE Recursive query will produce output something like the following:

EmployeeID  LastName             ReportsTo
----------- -------------------- -----------
2           Fuller               NULL
10          Caroler              NULL
12          Clause               NULL
16          Stegman              NULL
18          Autin                NULL
22          Davolio              NULL
24          Fuller               NULL
28          Suyama               NULL
30          King                 NULL
34          Fisherman            NULL
35          Apex                 NULL
39          Footer               NULL
40          Billings             NULL
53          Bruke                NULL
54          Smarba               NULL
55          Smarba               NULL
56          asd                  NULL
57          Schecter             NULL
58          Schecter             NULL
1           Davo                 2
3           Leverling            2
4           Peacock              2
5           Buchanan             2
8           Abrams               2
13          Turner               2
14          Bush                 2
15          Hillman              2
20          JohnBoy              2
25          Leverling            2
26          Peacock              2
27          Buchanann            2
32          Rainman              2
36          Longarm              2
37          Lu                   2
38          Manner               2
41          JohnBoy              2
6           Suyama               5
7           King                 5
9           Smith                5
21          Plummer              5
33          Eater                5
42          Singer               5