SQL Server Recursive CTE

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 :

1. Invocation – This is the statement using the CTE.
2. Anchor Member – This part executes first and is called one time only.
3. 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.
4. Termination condition check – The termination check to ensures the query stops.

Syntax

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.

WITH cte

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

 

 

Also Read..

CTE ( Common Table Expression)