Recursive Common Table Expressions

Common tables expressions (CTE) are pretty simple to understand but there is a recursive version that is a little more complicated.  They are good if you’ve got hierarchical data stored in a self-referencing table.  For example, you may have an EmployeeId column and a ManagerId column in the same table.  The ManagerId value of a single row references an EmployeeId in another row.  You may have a management hierarchy consisting of many levels.  Recursive CTEs are a good way to get this hierarchical data out in one query and it doesn’t care how many levels there are in the hierarchy.  There is a maximum recursion level vaue that you can set that stops your recursive CTEs from entering an infinite loop and I’ll show you this later along with an example of a more complicated CTE.  Firstly, here’s a very basic example which shows the structure of a recursive CTE.

This is from a comment by elvedrano on the following link: http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

WITH cte
AS ( 
 SELECT -- anchor member
 1 AS n
UNION ALL
SELECT -- recursive member
 n + 1
 FROM cte
 WHERE n < 50 -- terminator
)
SELECT
*
FROM cte;

The comments in this T-SQL highlight the different parts of the recursive CTE.  The three parts you need to include are the anchor member, the recursive member and the terminator.

The anchor member is usually a select statement that gets the first level in the hierarchy.  You then add a second select statement, separated by a UNION ALL, that references the CTE and the column that controls the hierarchy.  You can then add an optional terminator that will limit the recursion to a certain number of level.  If you only have a set number of levels you can omit the terminator.  The anchor member, recursive member and terminator all existing within the CTE declaration and you can then just select straight from the CTE to see the hierarchical results.

Here’s an example using employees and managers.

CREATE TABLE dbo.Employees
(
 EmployeeId INT NOT NULL PRIMARY KEY,
 EmployeeName NVARCHAR(50) NOT NULL,
 EmployeeRole NVARCHAR(50) NOT NULL,
 ManagerId INT NULL
);
INSERT INTO dbo.Employees (EmployeeId, EmployeeName, EmployeeRole, ManagerId)
VALUES
(1, 'Mickey Mouse', 'CEO', 1),
(2, 'Minnie Mouse', 'Chairman', 1),
(3, 'Donald Duck', 'CTO', 1),
(4, 'Tom the Cat', 'Head of Sales', 2),
(5, 'Jerry the Mouse', 'Head of Development', 3),
(6, 'Spot the Dog', 'Head of Testing', 3),
(7, 'Officer Dibble', 'Sales Manager', 4),
(8, 'Fred Flintstone', 'Sales', 7),
(9, 'Wilma Flintstone', 'Sales', 7),
(10, 'Barney Rubble', 'Sales', 7),
(11, 'Betty Rubble', 'Sales', 7),
(12, 'Captain Blackadder', 'Developer', 5),
(13, 'Baldrick', 'Developer', 5),
(14, 'Captain Darling', 'Tester', 6);
WITH EmployeeHierarchy AS (
 SELECT EmployeeId, EmployeeName, EmployeeRole, ManagerId, 1 AS Level
 FROM dbo.Employees
 WHERE EmployeeId = ManagerId
UNION ALL
SELECT E.EmployeeId, E.EmployeeName, E.EmployeeRole, E.ManagerId, EH.Level + 1 AS Level
 FROM dbo.Employees E
 INNER JOIN EmployeeHierarchy EH ON EH.EmployeeId = E.ManagerId
 WHERE E.EmployeeId <> E.ManagerId
)
SELECT *
FROM EmployeeHierarchy
DROP TABLE dbo.Employees;

RecursiveCTE

With recursive CTEs it’s possible to enter an endless loop.  You can prevent this from happening by using the MAXRECURSION option in the query.  We can modify the original simple CTE by removing the terminator and adding the MAXRECURSION option.  This query returns the same results, although it does return an error once the max recursion level is reached.

WITH cte
AS ( 
 SELECT -- anchor member
 1 AS n
UNION ALL
SELECT -- recursive member
 n + 1
 FROM cte
)
SELECT
*
FROM cte
OPTION (MAXRECURSION 49)
Advertisements

One thought on “Recursive Common Table Expressions

  1. Pingback: Exam 70-464 | Simon Learning SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s