Using SQL Server’s MAXRECURSION to Guard Against Infinite Loops in a CTE

When you work with recursive common table expressions (CTEs) in SQL Server, the engine will keep feeding rows back into the CTE until there’s nothing left to process. In most cases that works fine, but if the recursion logic is flawed, or the data contains a cycle, the query can spin forever. That’s where the MAXRECURSION hint comes to the rescue. It tells SQL Server to stop after a certain number of iterations, protecting you from runaway queries and giving you a clear error if something goes wrong.

Why Use `MAXRECURSION`?

It’s certainly possible to run a CTE without MAXRECURSION, but here are some reasons you should consider it when using recursive CTEs:

Safety net: Without a limit, a badly written recursion can consume CPU and memory until the server throttles or crashes.
Predictability: You know exactly how deep the recursion can go, which helps with performance tuning.
Debugging aid: When the limit is hit, SQL Server throws an error that includes the iteration count. This can be useful for spotting unexpected cycles.

How Recursive CTEs are Constructed

A recursive CTE has three parts:

Anchor member – the starting row set.
Recursive member – references the CTE itself and adds new rows based on the previous iteration.
Termination condition – usually a WHERE clause that eventually stops the recursion.

If the termination condition never becomes false, the recursion never ends. Adding MAXRECURSION forces a hard stop.

Example

A common use case for recursive CTEs is when a simple employee table records each person’s manager. Given the manager is also an employee, each employee will have a reference to another employee (i.e., the manager) in the same table. You want to list all sub‑ordinates under a given manager, but you also want to guard against accidental circular reporting structures, you could use the MAXRECURSION to guard against such situations.

Let’s create a basic Employees table:

CREATE TABLE Employees (
    EmployeeID   INT PRIMARY KEY,
    Name         NVARCHAR(50),
    ManagerID    INT NULL      -- NULL means top‑level boss
);

Now insert some sample data:

INSERT INTO Employees (EmployeeID, Name, ManagerID) VALUES
(1, N'Ainsley',   NULL),   -- CEO
(2, N'Barbara',     1),
(3, N'Carol',   2),
(4, N'Dave',    2),
(5, N'Eve',     3),
(6, N'Frank',   5);       -- Deep chain, but still finite

Now we’ll build a CTE that walks down the hierarchy from a chosen manager (say, Ainsley with EmployeeID = 1). We’ll cap the recursion at 10 levels, which is more than enough for this dataset.

WITH OrgChart AS (
    -- Anchor: start with the selected manager
    SELECT
        EmployeeID,
        Name,
        ManagerID,
        0 AS LevelDepth          -- level counter for readability
    FROM Employees
    WHERE EmployeeID = 1          -- start point

    UNION ALL

    -- Recursive part: find direct reports of the previous level
    SELECT
        e.EmployeeID,
        e.Name,
        e.ManagerID,
        oc.LevelDepth + 1
    FROM Employees e
    JOIN OrgChart oc
        ON e.ManagerID = oc.EmployeeID
    WHERE oc.LevelDepth < 10      -- optional extra safety filter
)
SELECT *
FROM OrgChart
OPTION (MAXRECURSION 10);        -- enforce the same limit at the query level

Result:

EmployeeID  Name     ManagerID  LevelDepth
----------  -------  ---------  ----------
1           Ainsley  null       0         
2           Barbara  1          1         
3           Carol    2          2         
4           Dave     2          2         
5           Eve      3          3         
6           Frank    5          4

Here’s what happened:

Iteration 0 – the anchor returns Ainsley (LevelDepth = 0).
Iteration 1 – the recursive member finds Barbara (Ainsley’s direct report).
Iteration 2 – Carol and Dave appear as Barbara’s reports.
Subsequent iterations – the chain continues until Frank is reached at depth 5.

Because we specified MAXRECURSION 10, SQL Server will abort the query if it ever tries to go beyond ten passes. In this case the query finishes cleanly after five passes.

Let’s change MAXRECURSION to 3 in order to trigger a maximum recursion error:

WITH OrgChart AS (
    SELECT
        EmployeeID,
        Name,
        ManagerID,
        0 AS LevelDepth
    FROM Employees
    WHERE EmployeeID = 1

    UNION ALL

    SELECT
        e.EmployeeID,
        e.Name,
        e.ManagerID,
        oc.LevelDepth + 1
    FROM Employees e
    JOIN OrgChart oc
        ON e.ManagerID = oc.EmployeeID
    WHERE oc.LevelDepth < 10
)
SELECT *
FROM OrgChart
OPTION (MAXRECURSION 3);

Output:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 3 has been exhausted before statement completion.

The error message is self-explanatory. The statement was terminated before it completed all recursions.

What If a Cycle Exists?

Imagine someone accidentally entered a circular reference:

UPDATE Employees SET ManagerID = 6 WHERE EmployeeID = 1;  -- CEO now reports to Frank

Running the same CTE without a limit would cause an endless loop. Well, it would if we hadn’t included WHERE oc.LevelDepth < 10 in the query.

Here’s what happens if we keep that line and use the original query:

WITH OrgChart AS (
    SELECT
        EmployeeID,
        Name,
        ManagerID,
        0 AS LevelDepth
    FROM Employees
    WHERE EmployeeID = 1

    UNION ALL

    SELECT
        e.EmployeeID,
        e.Name,
        e.ManagerID,
        oc.LevelDepth + 1
    FROM Employees e
    JOIN OrgChart oc
        ON e.ManagerID = oc.EmployeeID
    WHERE oc.LevelDepth < 10
)
SELECT *
FROM OrgChart
OPTION (MAXRECURSION 10);

Result:

EmployeeID  Name     ManagerID  LevelDepth
----------  -------  ---------  ----------
1           Ainsley  6          0         
2           Barbara  1          1         
3           Carol    2          2         
4           Dave     2          2         
5           Eve      3          3         
6           Frank    5          4         
1           Ainsley  6          5         
2           Barbara  1          6         
3           Carol    2          7         
4           Dave     2          7         
5           Eve      3          8         
6           Frank    5          9         
1           Ainsley  6          10        

13 row(s) returned

We can see that it’s starting to loop over and over, until it’s abruptly stopped. In any case, the results are out of whack.

Let’s remove that WHERE oc.LevelDepth < 10 part:

WITH OrgChart AS (
    SELECT
        EmployeeID,
        Name,
        ManagerID,
        0 AS LevelDepth
    FROM Employees
    WHERE EmployeeID = 1

    UNION ALL

    SELECT
        e.EmployeeID,
        e.Name,
        e.ManagerID,
        oc.LevelDepth + 1
    FROM Employees e
    JOIN OrgChart oc
        ON e.ManagerID = oc.EmployeeID
)
SELECT *
FROM OrgChart
OPTION (MAXRECURSION 10);

Output:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 10 has been exhausted before statement completion.

This time we got the maximum recursion error, as expected.

This is a handy error, as it instantly tells you there’s a data integrity problem, which will prompt you to fix the cycle. But it pays to be careful when implementing other safe guards, as they could inadvertently mask the issue without you noticing.

Why Use MAXRECURSION?

How Recursive CTEs are Constructed

Example

What If a Cycle Exists?

Why Use `MAXRECURSION`?