Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Recursive CTEs

  • 19 June 2013
  • Author: Kathi Kellenberger
  • Number of views: 11949

I took quite a few computer science courses while working on my master’s degree. I learned how to create and manipulate many types of data structures like binary trees and linked lists. One of the techniques I learned in these classes was recursion. Recursion means that a process calls itself. For example, a recursive function has a call to itself within the function.

Recursion can be used to traverse a hierarchy, like a network, and touch every possible node or point. To keep the process from running forever, there must be some kind of end condition.

As a quick example, take a look at the network in the figure.  To traverse the network, you would start at node 1. From node 1, you would travel to node 2. From node 2 you would travel to node 3 and finally to node 4. At node 4, there is no place to go – our end condition – so you would head back up the network to node 1. At node 2, you can also travel to node 5, another endpoint. From node 1, there are two additional paths, node 8 and node 6 to 7.

Common Table Expressions (CTE), introduced with SQL Server 2005, can be called recursively. To learn how to write a CTE, see this blog post. While recursion is an interesting technique, it is rarely needed.  When should you use this? Use it when the data has a natural hierarchy, such as an organizational chart or family tree. The 2005 version of AdventureWorks contained data that had such a hierarchy, the Employees table. In that table, each employee had a manager who was also an employee. With the introduction of the HierarchyID data type in 2008, Microsoft changed the table so that we can no longer use the recursive CTE. 

In my book, Beginning T-SQL 2012, I wanted to demonstrate recursive CTEs. The example from the book creates a temp table with the hierarchal data.  Here is the code for the temp table:

[EmployeeID] [int] NOT NULL,
[ContactID] [int] NOT NULL,
[ManagerID] [int] NULL,
[Title] [nvarchar](50) NOT NULL);

INSERT INTO #Employee (EmployeeID, ContactID, ManagerID, Title) VALUES
(1, 1209, 16,'Production Technician - WC60'),
(2, 1030, 6,'Marketing Assistant'),
(3, 1002, 12,'Engineering Manager'),
(4, 1290, 3,'Senior Tool Designer'),
(5, 1009, 263,'Tool Designer'),
(6, 1028, 109,'Marketing Manager'),
(7, 1070, 21,'Production Supervisor - WC60'),
(8, 1071, 185,'Production Technician - WC10'),
(9, 1005, 3,'Design Engineer'),
(10, 1076, 185,'Production Technician - WC10'),
(11, 1006, 3,'Design Engineer'),
(12, 1001, 109,'Vice President of Engineering'),
(13, 1072, 185,'Production Technician - WC10'),
(14, 1067, 21,'Production Supervisor - WC50'),
(15, 1073, 185,'Production Technician - WC10'),
(16, 1068, 21,'Production Supervisor - WC60'),
(17, 1074, 185,'Production Technician - WC10'),
(18, 1069, 21,'Production Supervisor - WC60'),
(19, 1075, 185,'Production Technician - WC10'),
(20, 1129, 173,'Production Technician - WC30'),
(21, 1231, 148,'Production Control Manager'),
(22, 1172, 197,'Production Technician - WC45'),
(23, 1173, 197,'Production Technician - WC45'),
(24, 1113, 184,'Production Technician - WC30'),
(25, 1054, 21,'Production Supervisor - WC10'),
(109, 1287, NULL, 'Chief Executive Officer'),
(148, 1052, 109, 'Vice President of Production'),
(173, 1058, 21, 'Production Supervisor - WC30'),
(184, 1056, 21, 'Production Supervisor - WC30'),
(185, 1053, 21, 'Production Supervisor - WC10'),
(197, 1064, 21, 'Production Supervisor - WC45'),
(263, 1007, 3, 'Senior Tool Designer');

Each row inserted into the table has an EmployeeID. Each row also has a ManagerID that points to a different EmployeeID row. One row, the CEO of the company doesn’t have a manager. The ManagerID for that row is NULL.

To get started writing the recursive CTE, you have to come up with the anchor member. The anchor member is the first level. In the network example, that would be Node 1. In the employee data, that is the CEO of the company, the row with a NULL ManagerID as returned in the next query.

SELECT EmployeeID, ManagerID, Title, 0 AS Level
FROM #Employee


Now that we have the anchor member, convert the query to CTE form. This query returns identical results.

;WITH OrgChart AS  (
SELECT EmployeeID, ManagerID, Title, 0 AS Level
FROM #Employee
SELECT EmployeeID, ManagerID, Title, Level
FROM OrgChart;

The next step is to determine how the recursive part of the query, or recursive member, will join to the anchor member. In this case, the EmployeeID from the anchor will join to the ManagerID of the recursive member. When this CTE runs, the recursive member will run once for each level in the data. The next code sample, which will not run on its own, is the actual recursive member.


SELECT a.EmployeeID, a.ManagerID, a.Title, b.Level + 1
FROM #Employee a JOIN OrgChart b on b.EmployeeID = a.ManagerID

The results of the anchor and recursive members are combined with UNION ALL and must have the same number of columns.  By adding 1 to the Level value returned from the previous results, the current level can be returned. Now to add the recursive member to the CTE:

;WITH OrgChart AS  (
SELECT EmployeeID, ManagerID, Title, 0 AS Level
FROM #Employee
SELECT a.EmployeeID, a.ManagerID, a.Title, b.Level + 1
FROM #Employee a JOIN OrgChart b on a.ManagerID = b.EmployeeID
SELECT EmployeeID, ManagerID, Title, Level
FROM OrgChart;

Just like a recursive function, a recursive CTE must have an end condition. In this example, there is a natural end. Once an employee does not manage any other employees, no further rows are returned down that path. Eventually, no additional rows are returned and the recursion stops.

To make this example more interesting, we can add another column that displays the hierarchy or path from the CEO to each employee. At the anchor member add a Node column populated with a slash. At the recursive member, append the ManagerID and another slash. Here is the code:

;WITH OrgChart (EmployeeID, ManagerID, Title, Level,Node)
AS (SELECT EmployeeID, ManagerID, Title, 0,
FROM #Employee
SELECT a.EmployeeID, a.ManagerID,a.Title, b.Level + 1,
CONVERT(VARCHAR,a.ManagerID) + '/')
FROM #Employee AS a
INNER JOIN OrgChart AS b ON a.ManagerID = b.EmployeeID
SELECT EmployeeID, ManagerID, Title, Level, Node
FROM OrgChart


This example fits the recursive CTE technique very well. But, it is possible to write a recursive CTE without an end condition, usually in error. As a precaution, the recursive member will only be called 100 times by default. There is an option called MAXRECURSION that you can set if you wish to override the 100 call limit.

The recursive CTE technique is meant for hierarchical data. It is used very rarely, and the performance is not great. Keep it in mind for those rare circumstances where it actually is a good idea.


Categories: SQL Server
Rate this article:
No rating

Please login or register to post comments.