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;
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)