SQL Server › Common Table Expressions (CTEs)

What is a recursive common table expression, and what kind of problems is it commonly used to solve?

Intermediate

Answer

A recursive CTE references itself within its own definition, repeatedly building on its previous results, which makes it especially useful for working with hierarchical data, such as an organizational chart showing employees and their managers, or generating a sequence of numbers or dates.

WITH EmployeeHierarchy AS (
  SELECT EmployeeId, ManagerId, Name, 0 AS Level
  FROM Employees WHERE ManagerId IS NULL
  UNION ALL
  SELECT e.EmployeeId, e.ManagerId, e.Name, eh.Level + 1
  FROM Employees e
  JOIN EmployeeHierarchy eh ON e.ManagerId = eh.EmployeeId
)
SELECT * FROM EmployeeHierarchy;

Real-world example A human resources system uses a recursive CTE to display a complete organizational chart, showing every employee's reporting level from the top executive down to individual team members, all in a single query.

Common follow-ups: What happens if a recursive CTE accidentally creates an infinite loop?;How do you limit the maximum recursion depth in SQL Server?

Data Types & Schema Design;Subqueries