A Recursive CTE (Common Table Expression) is a CTE that references itself. In doing so, the initial CTE is repeatedly executed, returning subsets of data, until it returns the complete result set.
The recursive CTE (Common Table Expression) is useful when working with hierarchical data because the CTE continues to execute until the query returns the entire hierarchy.
By default maximum recursion level supported by CTE is 100. That can be changed by using MAXRECURSION hint.
This recursive CTE must consists following parts in its definitions :
- Invocation – This is the statement using the CTE.
- Anchor Member – This part executes first and is called one time only.
- Recursive Member – This portion of the query is repeatedly executed until no rows are returned. The results of each execution are unioned with the prior results.
- Termination condition check – The termination check to ensures the query stops.
WITH cte_name (col1, col2, …) AS ( Sql query -- Anchor member UNION ALL Sql query -- Recursive member that referenced to cte_name, must have --termination check ) -- Statement using the CTE SELECT * FROM cte_name -- Invocation
Lets Look at an example of Using Recursive CTE in SQL Server.
Generate numbers from 1 to 100 using recursive CTE
Following statement uses recursive CTE and returns numbers from 1 to 100 .
WITH cte AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM cte WHERE n < 100 ) SELECT n FROM cte;
CTEs default maximum recursion level
By default maximum recursion level supported by CTE is 100.
Lets check if we exceeds the recursion level greater than 100, we will modify above recursive CTE query for recursion level at 101 then see what happens.
WITH cte AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM cte WHERE n <= 101 ) SELECT n FROM cte;
As we see , It returns an error saying ‘maximum recursion 100 has been exhausted before statement completion.’
It means by default CTE has default recursion level is 100.
Change the CTE maximum Recursion Level
To change the CTE maximum recursion level , We use the MAXRECURSION query hint.
The Maximum recursion level that we can specify with MAXRECURSION query hint is 32,767.
Lets execute above query for recursion level up to 101 using MAXRECURSION query hint.
WITH cte AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM cte WHERE n <= 101 ) SELECT n FROM cte OPTION (MAXRECURSION 101)
The Maximum Number of Recursion level that we can specify with MAXRECURSION is 32,767.
Suppose we have a requirement where recursion level can exceed the number 32767 , Lets try to generate the numbers more than 32767 that is 32768.
We can do it by specifying the MAXRECURSION query hint value as 32768 .
WITH cte AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM cte WHERE n <= 32768 ) SELECT n FROM cte OPTION (MAXRECURSION 32768)
As we see above it returns an error of MAXRECURSION Option exceeds the allowed maximum of 32767 .
To avoid such error when MAXRECURSION Option exceeds the maximum recursion level -32767 , we specify MAXRECURSION value as 0.
AS (SELECT 1 AS n
UNION ALL SELECT n + 1
FROM cte WHERE n <= 32768
SELECT n FROM cte
OPTION (MAXRECURSION 0)
MAXRECURSION query hint value 0 means no limit to the recursion level, if we are specifying MAXRECURSION =0 , in this case we have to make sure that our query is not resulting in an infinite recursion level.
Get the Hierarchical structure of all employees
Using recursive CTE you can get the get the hierarchical structure of all employees, lists employee with their reporting manager.
To demonstrate this, lets create a employee table that contains employee along with their reporting manager details.
CREATE TABLE dbo.Employee_Data ( EmpID INT NULL, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, JobTitle VARCHAR(50) NOT NULL, ManagerID INT NOT NULL ); INSERT INTO dbo.Employee_Data (EmpID, FirstName, LastName, JobTitle, ManagerID) VALUES (1,'Subham','Jindal','CEO', 0), (2,'Ramesh','Khatri','AVP',1), (3,'Rohit','Kumar','HR Manager', 2), (4,'Sujoy','Ghosh','Software Developer', 5), (5,'Lokesh','Kumar','Team Lead', 11), (6,'Mandeep','Kaur','Software Developer', 5), (7,'Rajat','Prakash','Software Developer', 5), (8,'Ram','Tijori','Software Developer', 5), (9,'Sanket','Singh','Software Developer', 5), (10,'Vineesh','Singh','Software Developer', 5), (11,'Manhor','Aggarwal','Program Manager', 2);
Now, we have a table named Employee_Data as shown below.
SELECT * FROM dbo.Employee_Data
Following SQL Statement uses recursive CTE and returns the name hierarchical structure of employees.
;WITH rec_CTE (EmpID, ManagerID, FirstName,LastName, JobTitle, Hierarchical_Level) AS ( SELECT EmpID, ManagerID, FirstName, LastName, JobTitle, 0 FROM dbo.Employee_Data WHERE ManagerID=0 UNION ALL SELECT EmpData.EmpID, EmpData.ManagerID, EmpData.FirstName, EmpData.LastName, EmpData.JobTitle, cte.Hierarchical_Level+1 FROM dbo.Employee_Data EmpData INNER JOIN rec_CTE cte ON EmpData.ManagerID=cte.EmpID ) SELECT cte.EmpID, cte.FirstName + ' '+ cte.LastName AS EmployeeName, cte.ManagerID, MgrTag.FirstName + ' ' + MgrTag.LastName as [ManagerName], cte.Hierarchical_Level, cte.JobTitle FROM rec_CTE cte LEFT OUTER JOIN dbo.Employee_Data MgrTag ON cte.ManagerID = MgrTag.empID ORDER BY cte.Hierarchical_Level