Recursive CTEs in SQL Server: Safe Hierarchy Traversal

An organizational tree or category hierarchy often starts as a table containing an identifier and a parent identifier. Reading one level is easy; reading every descendant requires repeated traversal. A recursive CTE expresses that traversal clearly, but it does not establish that the stored relationships actually form a valid tree.

Define the model first. Does each node have one parent? Can there be several roots? Are disconnected nodes allowed? A foreign key can require that a referenced parent exists, but it does not prevent a longer cycle. These distinctions determine both the query and the validation needed around writes.

Separate the starting set from the expansion

The example starts at node 1 and follows children. It includes an explicit visited path and a recursion limit.

DECLARE @Nodes table (NodeId int PRIMARY KEY, ParentId int NULL, Name nvarchar(50));
INSERT @Nodes VALUES (1,NULL,N'Company'),(2,1,N'Operations'),
(3,1,N'Engineering'),(4,2,N'Database team');

;WITH Tree AS (
    SELECT NodeId, ParentId, Name, 0 AS Depth,
           CAST('/' + CONVERT(varchar(11),NodeId) + '/' AS varchar(max)) AS Visited
    FROM @Nodes WHERE NodeId = 1
    UNION ALL
    SELECT n.NodeId, n.ParentId, n.Name, t.Depth + 1,
           CAST(t.Visited + CONVERT(varchar(11),n.NodeId) + '/' AS varchar(max))
    FROM @Nodes AS n
    JOIN Tree AS t ON n.ParentId = t.NodeId
    WHERE CHARINDEX('/' + CONVERT(varchar(11),n.NodeId) + '/', t.Visited) = 0
)
SELECT NodeId, ParentId, Name, Depth, Visited
FROM Tree
ORDER BY Visited
OPTION (MAXRECURSION 100);

The anchor produces Company at depth zero. The recursive member finds Operations and Engineering, then Database team under Operations. ParentId is the direction of traversal: reversing that join answers a different question, such as finding ancestors.

The path contains delimited identifiers. Delimiters matter because searching for the plain text 1 would also match 11 or 21. The recursive row is excluded when its identifier already exists on that branch's path. This protects the traversal from repeatedly visiting a cycle, but exclusion is not a repair of the underlying relationship.

Anchor and recursive columns must have compatible types. Both path expressions explicitly use varchar(max); otherwise a short anchor string can establish a type that does not match the growing recursive expression. The path stores numeric identifiers, while display names remain Unicode.

ORDER BY Visited provides a convenient example ordering, not a universal sibling order. Text paths sort lexically, so identifier 10 can sort before 2. If siblings need a business-defined order, model and construct that order deliberately. Recursive production order alone does not guarantee final presentation order.

Treat limits and exclusions as evidence

MAXRECURSION 100 is a guard, not a declaration that every organization has at most 100 levels. If the legitimate model is deeper, choose a justified bound and test it. Hitting the limit should fail the operation visibly; a client must not accept an incomplete result as a complete hierarchy.

Removing the bound with MAXRECURSION 0 removes that safety limit. It does not prove the data is acyclic. Keep cycle handling and data validation even when a larger legitimate depth requires a different setting.

The visited-path predicate silently stops a repeated branch. For administrative reporting, separately detect and report cycles rather than simply presenting the shortened tree as healthy. A write-side validation should check whether moving a node underneath a proposed parent would make the node its own ancestor.

Starting from one root also says nothing about disconnected components. Compare reachable identifiers with the intended population when auditing the full hierarchy. A cycle disconnected from every root can be completely absent from a root-based report. Missing nodes may be a data issue rather than a query performance problem.

Make the access path fit repeated expansion

On a permanent adjacency-list table, an index beginning with ParentId can support finding children during expansion. Include or key additional columns according to the real query. The primary key on NodeId serves a different lookup direction and does not automatically make descendant searches efficient.

Measure fanout and total descendants as well as depth. A shallow hierarchy with millions of children can cost more than a deep narrow chain. The visited string also grows with depth and is copied as traversal progresses, so it is a useful demonstrator rather than free cycle detection.

For frequent large subtree reads, evaluate whether hierarchyid, a closure table, or another maintained representation fits the read-write balance. Such designs move complexity into updates and consistency checks; they do not remove it.

Test a single node, siblings, a deep chain, multiple roots, disconnected nodes, and an intentional cycle in disposable data. Compare the actual node identifiers and expected depth. A correct hierarchy query must explain both what it returns and why some stored nodes are outside its result.

Technical references: Microsoft Learn: Recursive queries · Microsoft Learn: MAXRECURSION.