SQL Server Interview Question #77

What is a recursive CTE?

Joins, Subqueries, CTEs & Set Operators Mid-Level Intermediate

Detailed Explanation

A recursive CTE references itself and is commonly used to traverse hierarchical data such as employee reporting structures, organizational trees, or category hierarchies.

It normally contains an anchor member that returns the starting rows and a recursive member that repeatedly joins to the previous result. UNION ALL combines the members.

SQL Server's default MAXRECURSION limit for a recursive query is 100 unless changed with an OPTION clause. Cycles and excessive recursion must be considered in production designs.

Code Example

WITH EmployeeHierarchy AS
(
    SELECT Id, Name, ManagerId, 0 AS LevelNo
    FROM dbo.Employees
    WHERE ManagerId IS NULL

    UNION ALL

    SELECT e.Id, e.Name, e.ManagerId, h.LevelNo + 1
    FROM dbo.Employees AS e
    INNER JOIN EmployeeHierarchy AS h
        ON e.ManagerId = h.Id
)
SELECT *
FROM EmployeeHierarchy
OPTION (MAXRECURSION 100);