How Does a Recursive CTE Execute Line by Line in SQL Server?

Line-by-Line Execution of a Recursive CTE

Recursive Common Table Expressions (CTEs) are often difficult to grasp, but they can be understood by breaking down their execution line by line.

Anchor

SELECT  id, Name, ParentID, CAST(Name AS VARCHAR(1000)) AS Path
FROM    @tbl
WHERE   ParentId IS NULL

• The anchor selects the top-level nodes, where ParentId is NULL.
• It projects the following columns: id, Name, ParentID, and a Path column initialized with the node's name.

Recursive Member

SELECT  t.id, t.Name, t.ParentID, CAST((a.path + '/' + t.Name) AS VARCHAR(1000)) AS "Path"
FROM    @tbl AS t
JOIN    abcd AS a
ON      t.ParentId = a.id

• The recursive member selects child nodes by joining the temporary table abcd with @tbl based on ParentId.
• It appends the child node's name to the parent's Path.

Endless UNION ALL

Think of the recursive CTE as an endless UNION ALL that expands horizontally, adding new levels of hierarchy with each iteration. For example, abcd2 would include child nodes of abcd1, abcd3 would include child nodes of abcd2, and so on.

Each Iteration

• The anchor is never skipped in later iterations. It always selects the top-level nodes.
• The recursive member joins child nodes to the previously selected nodes, creating a hierarchical structure.
• As each iteration progresses, the Path column is updated to reflect the correct parent-child relationships.

Stopping Condition

Theoretically, a recursive CTE can run indefinitely. However, SQL Server prevents infinite recordsets by implementing a stopping condition. When a recursive member fails to produce any new rows, the iteration stops.

In this example, when the recursive member fails to find child nodes for level abcd6, the iteration ends.

Final Result

The final result is a complete hierarchical tree, starting with the top-level nodes and expanding down to the lowest levels. The Path column provides the full path to each node in the hierarchy.

The above is the detailed content of How Does a Recursive CTE Execute Line by Line in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!