Understanding Recursive CTEs in SQL Server: An Overview

A recursive Common Table Expression (CTE) in Microsoft SQL Server is a query construct used to handle hierarchical or recursive data relationships. Recursive CTEs are particularly useful for queries involving data that is structured in a parent-child relationship or needs to be processed in a hierarchical manner.

Key Concepts of Recursive CTE

1. Anchor Member: The initial query in the CTE that establishes the starting point of the recursion. It defines the base set of data from which the recursion will begin.
2. Recursive Member: This part of the CTE references the CTE itself and defines how to recursively join or process rows to build upon the anchor member results.
3. Termination Condition: The recursion continues until the recursive member returns no additional rows, thus stopping the recursion and ending the CTE.

Syntax

Here’s the basic syntax of a recursive CTE:

WITH CTE_Name (Column1, Column2, ...)
AS
(
    -- Anchor member: Base result set
    SELECT Column1, Column2, ...
    FROM TableName
    WHERE [Initial condition]

    UNION ALL

    -- Recursive member: Recursively build on the results
    SELECT t.Column1, t.Column2, ...
    FROM TableName t
    INNER JOIN CTE_Name cte
    ON t.SomeColumn = cte.SomeColumn
)
-- Final SELECT statement
SELECT *
FROM CTE_Name;

Practical use cases of Recursive CTE

Recursive Common Table Expressions (CTEs) are quite versatile and can be used in various practical scenarios where hierarchical or recursive data needs to be processed.

Here are some common use cases:

Example 1: Employee Hierarchy

Scenario: You have an Employees table with hierarchical reporting relationships. You want to find all employees under a particular manager, including sub-managers and their subordinates.

Sample data: Let’s create a table and insert some values to demonstrate recursive CTE.

CREATE TABLE [dbo].Employee(
	[empid] [int] NOT NULL,
	[employee name] [nvarchar](50) NOT NULL,
	[managerid] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].Employee ([empid], [employee name], [managerid]) VALUES (1, N'John', NULL)
INSERT [dbo].Employee ([empid], [employee name], [managerid]) VALUES (2, N'Charles', 1)
INSERT [dbo].Employee ([empid], [employee name], [managerid]) VALUES (3, N'Nicolas', 2)
INSERT [dbo].Employee ([empid], [employee name], [managerid]) VALUES (4, N'Neil', 2)
INSERT [dbo].Employee ([empid], [employee name], [managerid]) VALUES (5, N'Lynn', 1)
INSERT [dbo].Employee ([empid], [employee name], [managerid]) VALUES (6, N'Vince', 5)
INSERT [dbo].Employee ([empid], [employee name], [managerid]) VALUES (7, N'Claire', 6)


SELECT * FROM Employee;

Requirement : Now, let’s say we have a requirement to report a list of all managers in the hierarchy for a given employee. For example, if we give an employee ID = 7, we need to get a report of his manager, the manager’s manager and so on.

Solution:

In order to implement the solution for the above query, we need a recursive CTE.

WITH empCTE AS 
	(
  SELECT empId, 
         [employee name], 
	 managerId, 
	 0 AS emp_level		-- ANCHOR ELEMENT
  FROM dbo.Employee
  WHERE empId = 7
    
  UNION ALL			-- TO COMBINE THE RESULT, IN ORDER
  
  SELECT mgr.empId, 
         mgr.[employee name], 
         mgr.managerId, 
         emp.emp_level +1 AS emp_level
  FROM empCTE AS emp
  INNER JOIN dbo.Employee AS mgr  -- SELF JOIN
  ON emp.managerId = mgr.empId	  -- TERMINATION CHECK
	)
SELECT * FROM empCTE;

Result:

The above results explains a simple example of recursive CTE.

In real time, we have more data than the sample shown here. That’s why we need to set the maxrecursion.

MAXRECURSION

In SQL Server, the MAXRECURSION option controls the maximum number of recursion levels allowed in a recursive Common Table Expression (CTE). By default, SQL Server limits the number of recursion levels to 100. This is a safeguard to prevent unintentional infinite recursion and potential performance issues.

Syntax

You can set the MAXRECURSION option in your query to specify the maximum number of recursive calls allowed:

OPTION (MAXRECURSION n);

Here, n is the maximum number of recursion levels. For example, OPTION (MAXRECURSION 50) would limit recursion to 50 levels.

Setting MAXRECURSION to 0

Setting MAXRECURSION to 0 (OPTION (MAXRECURSION 0)) removes the limit on the number of recursion levels. This means that the recursion will continue until it produces no more results or encounters an error, such as an infinite loop or excessive resource usage.

Recursive CTE with MAXRECURSION:

WITH empCTE AS 
	(
  SELECT empId, 
        [employee name], 
	managerId, 
	0 AS emp_level		-- ANCHOR ELEMENT
  FROM dbo.Employee
  WHERE empId = 7
    
  UNION ALL			-- TO COMBINE THE RESULT, IN ORDER
  
  SELECT mgr.empId, 
         mgr.[employee name], 
         mgr.managerId, 
         emp.emp_level +1 AS emp_level
  FROM empCTE AS emp
  INNER JOIN dbo.Employee AS mgr		-- SELF JOIN
  ON emp.managerId = mgr.empId			-- TERMINATION CHECK
	)
SELECT * FROM empCTE
OPTION (MAXRECURSION 0);

In summary, MAXRECURSION 0 allows for unlimited recursion but should be used cautiously. It is essential to ensure that your recursive CTE is well-designed to avoid infinite loops and performance issues.

Another useful tip is to limit the levels of CTE, so that in will avoid infinite loops.

Limiting the Number of Recursion Levels

In SQL Server, when using a recursive Common Table Expression (CTE), you can control the number of recursion levels (or “hops”) to limit the depth of the recursion. This is particularly useful when you want to prevent excessive recursion depth which might lead to performance issues or even infinite loops.

Example: Limiting Recursion Levels

WITH empCTE AS 
	(
  SELECT empId, 
        [employee name], 
		managerId, 
		0 AS emp_level		-- ANCHOR ELEMENT
  FROM dbo.Employee
  where empid = 7
    
  UNION ALL			-- TO COMBINE THE RESULT, IN ORDER
  
  SELECT mgr.empId, mgr.[employee name], mgr.managerId, emp.emp_level +1 AS emp_level
  FROM empCTE AS emp
  INNER JOIN dbo.Employee AS mgr							-- SELF JOIN
  ON emp.managerId = mgr.empId						-- TERMINATION CHECK
	)
SELECT * FROM empCTE
WHERE emp_level <=3
ORDER BY managerId
OPTION (MAXRECURSION 3)

Explanation

1. Anchor Member: The base query selects the initial level (starting with a specific manager).
2. Recursive Member: Joins the CTE with the Employees table to add employees who report to those already selected, incrementing the level.
3. WHERE Level <= 3: Ensures that only records up to the specified depth (3 levels) are included in the final result set.
4. OPTION (MAXRECURSION 3): Limits the recursion to a maximum of 3 levels. This helps prevent excessive recursion that could occur if the data had unintended cycles or extremely deep hierarchies.

Result:

By setting a reasonable limit on recursion levels, you maintain control over the complexity and performance of recursive queries in SQL Server.

Example 2: Account Hierarchy

Scenario: You have an Account table with only from and to account information. There are no existing hierarchical reporting relationships. You want to flatten the structure as Parent account–>sub account 1–> etc.

Sample data: Let’s create an Account table and insert some values to demonstrate the above scenario using recursive CTE.

CREATE TABLE [dbo].Account(
	[fromid] [int] NOT NULL,
	[toid] [int] NOT NULL,
	[from account name] [nvarchar](50) NOT NULL,
	[to account name] [nvarchar](50) NOT NULL
) ON [PRIMARY]

INSERT [dbo].Account ([fromid], [toid], [from account name], [to account name]) VALUES (1000, 2000, N'Global Group', N'Global Private Lmt')
INSERT [dbo].Account ([fromid], [toid], [from account name], [to account name]) VALUES (2000, 3000, N'Global Private Lmt', N'Global Finanace Lmt')
INSERT [dbo].Account ([fromid], [toid], [from account name], [to account name]) VALUES (2000, 4000, N'Global Private Lmt', N'Global Education Lmt')
INSERT [dbo].Account ([fromid], [toid], [from account name], [to account name]) VALUES (2000, 5000, N'Global Private Lmt', N'Global Entertainment Lmt')
INSERT [dbo].Account ([fromid], [toid], [from account name], [to account name]) VALUES (4000, 6000, N'Global Education Lmt', N'Global Scolorship Lmt')
INSERT [dbo].Account ([fromid], [toid], [from account name], [to account name]) VALUES (5000, 7000, N'Global Entertainment Lmt', N'Global Media Lmt')

SELECT * FROM [dbo].Account

The sample data is presented as below,

Requirement: To flatten the hierachy and view the chain of accounts

Solution: Now, let’s right a recursive CTE to flatten the parent child hierarchy.

WITH accountCTE AS 
	(
	--Anchor member: start with each FromID as the top of the chain
  SELECT Fromid AS Parent, 
         Toid   AS Child, 
	 0      AS Child_level, 
	 CAST(Fromid AS varchar(max)) + '-->' + CAST(toid AS varchar(max)) AS Chain_account,
	 CAST([from account name] AS varchar(max)) + '-->' + CAST([to account name] AS varchar(max)) AS     Chain_account_name
  FROM dbo.Account  
  
  UNION ALL			-- TO COMBINE THE RESULT, IN ORDER
  --Recursive member: Join on ToID to find further children
  SELECT acte.child AS Parent,
         acc.toid   AS Child, 
	 acte.Child_level + 1 AS Child_level, 
	 acte.Chain_account + '-->' +  CAST(acc.toid AS varchar(max)) as Chain_account, 
	 acte.Chain_account_name + '-->' +  CAST(acc.[to account name] AS varchar(max)) as Chain_account_name
  FROM accountCTE AS acte
  INNER JOIN dbo.Account AS acc						
    ON acte.child = acc.fromid						
	)
SELECT * FROM accountCTE
WHERE Child_level <=6
ORDER BY Parent
OPTION (MAXRECURSION 6);

Result:

The output of the above query will be,

Summary

Recursive CTEs are useful in various scenarios where data is inherently hierarchical or requires traversal of relationships. They can be applied to organizational charts, hierarchical bill-of-materials, nested folder structures, self-referencing relationships like comments, and even complex graph traversal problems. When using recursive CTEs, ensure to test performance and edge cases to avoid inefficiencies and infinite recursion.

References.

1. Previous post on CTE: WHAT IS CTE – COMMON TABLE EXPRESSION IN MSSQL – Data Journal Entries (gjeremiah.com)
2. Other articles on recursive CTE : Recursive Queries using Common Table Expressions (CTE) in SQL Server (mssqltips.com)
3. Understanding SQL Server Recursive CTE By Practical Examples (sqlservertutorial.net)