SQL Server › Common Table Expressions (CTEs)

How do you prevent a recursive common table expression from running forever if the underlying data accidentally contains a circular reference?

Intermediate

Answer

SQL Server has a default maximum recursion limit of one hundred, which will stop the query and raise an error if that limit is exceeded, but you can also explicitly set a lower limit using the MAXRECURSION query hint, or set it to zero to allow unlimited recursion if you are confident the data does not contain any circular references.

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

Real-world example A data quality team sets a lower MAXRECURSION limit while testing a new organizational chart query, quickly catching a data entry error that had accidentally created a circular manager relationship between two employees.

Common follow-ups: What error does SQL Server return when the recursion limit is exceeded?;Is it ever appropriate to disable the recursion limit entirely?

Data Types & Schema Design;Error Handling with TRY CATCH