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 (nSELECT 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 can be specified 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 specify 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)