Recursive CTEs and hierarchies

From reporting lines to multilevel data.

UnderstandStudy an examplePractise

Understand the concept

A recursive CTE consists of an anchor set and a recursive member that references it. UNION ALL commonly combines successive levels. For the employee tree, start with the employee without a manager, then find children of each existing level.

Cycles in incorrect data can cause unending recursion. SQL Server applies a default recursion limit; use MAXRECURSION for a controlled bound. Do not use unlimited 0 without validating the data. Depth and path information aid diagnosis; cycle prevention needs deliberate design.

WHEN TO USE IT

Use it for organisational trees and multilevel relationships.

COMMON PITFALL

Using MAXRECURSION 0 without considering cycles.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT EmployeeId, FullName FROM lab.Employee WHERE ManagerId IS NULL;

Rasim is the anchor row.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Return Rasim’s employee tree with depth.

Show the solution and explanation

Worked solution

WITH T AS (SELECT EmployeeId, FullName, 0 AS Depth FROM lab.Employee WHERE EmployeeId = 1 UNION ALL SELECT e.EmployeeId, e.FullName, t.Depth + 1 FROM lab.Employee AS e JOIN T AS t ON e.ManagerId = t.EmployeeId) SELECT * FROM T ORDER BY Depth, EmployeeId OPTION (MAXRECURSION 20);

Depth indicates a hierarchy level; ORDER BY still determines display order.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.