Recursive CTE Cycles: Detect Loops and Depth Boundaries

Recursive CTE cycles can repeat nodes even when an organization has very few levels. Increasing the recursion limit does not identify the bad relationship. Carry a visited path, report an attempted repeat and distinguish it from an intentional depth boundary.

Recursive CTE cycles need unambiguous visited tokens

The example uses integer identifiers separated by slashes. Searching for /1/ will not confuse node 1 with node 11. The anchor and recursive expressions both cast the path to varchar(4000). Matching types are required for the corresponding recursive columns.

A repeated node is returned once with IsCycle = 1. The next iteration excludes that flagged row. Its path identifies the chain that attempted the repeat. Keep that evidence while reviewing the incorrect parent assignment.

Run the setup block first. It creates three temporary tables with the sample rows used in this post, one case at a time. Run the query after it in the same session.

DROP TABLE IF EXISTS #Walk, #Start, #Node;
CREATE TABLE #Node(CaseId int, NodeId int, ParentId int NULL, PRIMARY KEY(CaseId,NodeId));
CREATE TABLE #Start(CaseId int PRIMARY KEY, CaseName varchar(30), StartNode int);
INSERT #Start VALUES
 (1,'Three-node cycle',1),(2,'Valid two-node tree',4),(3,'Self-loop',1),
 (4,'Two-node cycle',1),(5,'Depth boundary',1),(6,'Delimited identifiers',1);
INSERT #Node VALUES
 (1,1,3),(1,2,1),(1,3,2),(1,4,NULL),(1,5,4),
 (2,4,NULL),(2,5,4),(3,1,1),(4,1,2),(4,2,1),
 (6,1,NULL),(6,11,1),(6,111,11);
;WITH Numbers AS
 (SELECT 1 AS n UNION ALL SELECT n+1 FROM Numbers WHERE n<23)
INSERT #Node SELECT 5,n,CASE WHEN n=1 THEN NULL ELSE n-1 END
FROM Numbers OPTION(MAXRECURSION 30);

DECLARE @MaximumLevel int=20;
 ;WITH Walk AS
 (SELECT n.CaseId,n.NodeId,n.ParentId,0 AS TreeLevel,
         CAST('/'+CONVERT(varchar(11),n.NodeId)+'/' AS varchar(4000)) AS Visited,
         CAST(0 AS bit) AS IsCycle
  FROM #Node AS n JOIN #Start AS s
   ON s.CaseId=n.CaseId AND s.StartNode=n.NodeId
  UNION ALL
  SELECT c.CaseId,c.NodeId,c.ParentId,p.TreeLevel+1,
         CAST(p.Visited+CONVERT(varchar(11),c.NodeId)+'/' AS varchar(4000)),
         CAST(CASE WHEN CHARINDEX('/'+CONVERT(varchar(11),c.NodeId)+'/',p.Visited)>0
                   THEN 1 ELSE 0 END AS bit)
  FROM #Node AS c JOIN Walk AS p
   ON c.CaseId=p.CaseId AND c.ParentId=p.NodeId
  WHERE p.IsCycle=0 AND p.TreeLevel<@MaximumLevel)
 SELECT *,CAST(CASE WHEN TreeLevel=@MaximumLevel AND IsCycle=0
                AND EXISTS(SELECT 1 FROM #Node AS c
                           WHERE c.CaseId=Walk.CaseId AND c.ParentId=Walk.NodeId)
              THEN 1 ELSE 0 END AS bit) AS DepthBoundary
 INTO #Walk FROM Walk OPTION(MAXRECURSION 100);

Then read the walk back. The first grid counts the rows, cycle flags and depth boundaries for each case. The second grid lists the complete path for the three-node cycle and any depth boundary.

SELECT s.CaseName,COUNT(*) AS ReturnedRows,SUM(CONVERT(int,w.IsCycle)) AS CycleRows,
       SUM(CONVERT(int,w.DepthBoundary)) AS BoundaryRows
FROM #Walk AS w JOIN #Start AS s ON s.CaseId=w.CaseId
GROUP BY s.CaseId,s.CaseName ORDER BY s.CaseId;
SELECT s.CaseName,w.NodeId,w.ParentId,w.TreeLevel,w.IsCycle,w.DepthBoundary,w.Visited
FROM #Walk AS w JOIN #Start AS s ON s.CaseId=w.CaseId
WHERE w.CaseId=1 OR w.DepthBoundary=1 ORDER BY w.CaseId,w.TreeLevel;

Separate a requested depth from an emergency limit

The level predicate intentionally stops traversal after level 20. DepthBoundary marks an unflagged boundary node that still has children. That result is a deliberately incomplete view. It does not establish that the unexplored relationships are valid.

MAXRECURSION 100 remains a finite execution boundary in the same query. Reaching a recursion limit raises an error. A successful bounded result and a recursion-limit failure represent different outcomes. Calling code must not treat partially received rows as a complete successful hierarchy.

Bound the path as well as the number of levels

An integer identifier can require eleven characters, including its sign. Each appended token adds a slash. This level-20 example has at most 21 tokens, comfortably inside the declared path width. Raising the depth requires revisiting that bound.

A truncated path can forget an earlier identifier and weaken cycle detection. This example assumes integer identifiers and a single-parent relationship. Other identifier types, delimiters and graph models need their own representation and tests.

Check recursive CTE cycles beyond parentless roots

Nodes 1, 2 and 3 form the deliberately seeded ring. Starting at node 1 produces the attempted path /1/2/3/1/. A separate parentless component contains nodes 4 and 5. Starting only from parentless roots misses the ring entirely.

The sample rows also include a self-loop, a two-node cycle and a valid two-node tree. A deeper chain reaches the intentional boundary. Identifiers 1, 11 and 111 test token separation. These cases exercise different failure modes instead of assuming one successful tree proves validity.

Repair the relationship after reviewing the evidence

A repeated node does not mean that its whole record should be deleted. Review the intended parent and repair the incorrect edge through a controlled application write. Check the affected component again afterward. This demonstration performs no repair against application data. When you are done, drop the temporary tables.

Validate hierarchy changes when they are written, as well as when they are read. An index can accelerate the traversal of a loop. It cannot make that loop a valid tree. Keep cycle detection, depth policy and performance review as separate decisions.

DROP TABLE IF EXISTS #Walk, #Start, #Node;

A loop is easier to fix once it has a name and a path.

A recursion limit is not a cycle check, it is only a brake on runaway queries.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.