层级数据庞大时递归查询报错及结果不符问题求助

问题背景 #

现有员工层级表数据如下：

INSERT INTO dbo.Employee (EmployeeID, EmployeeName, ManagerID, TeamLeadID)
VALUES
(1, 'M1', NULL, NULL),       -- Root manager (M1)
(2, 'M2', 1, NULL),          -- M2 reports to M1
(3, 'M3', 1, NULL),          -- M3 reports to M1
(4, 'T1', 2, NULL),          -- T1 is a team lead, reports to M2
(5, 'T1_1', 2, 4),           -- T1_1 is a team member reports to M2 and T1
(6, 'T1_2', 2, 4),           -- T1_2 reports to M2 and T1
(7, 'T1_3', NULL, 4),        -- T1_3 reports only to T1 (no manager)
(8, 'T2', 3, NULL),          -- T2 is a team lead, reports to M3
(9, 'T2_1', 3, 8),           -- T2_1 reports to M3 and T2
(10, 'T2_2', 3, 8);          -- T2_2 reports to M3 and T2

需求是根据指定员工ID获取其所有下属层级数据：

• 当参数为EmployeeID=1（M1）时，返回所有M1的层级数据：M1,M2,M3,T1,T1_1,T1_2,T1_3, T2, T2_1 and T2_2
• 当参数为M2（EmployeeID=2）时，返回M2的层级数据：M2,T1,T1_1,T1_2 and T1_3
• 当参数为T2_2（EmployeeID=10）时，仅返回自身记录

现有查询的问题 #

原递归查询代码：

;WITH Hierarchy AS 
(
    SELECT *  
    FROM dbo.Employee  
    WHERE employeeid = 1

    UNION ALL  

    SELECT e.*
    FROM dbo.Employee e 
    INNER JOIN Hierarchy h ON h.employeeid = e.ManagerID  
                           OR h.employeeid = e.TeamLeadID
)  
SELECT *
FROM Hierarchy H
OPTION (MAXRECURSION 10000);

运行时触发错误：

The statement terminated. The maximum recursion 10000 has been exhausted before statement completion.

同时存在以下核心问题：

1. 无限递归循环：员工可能同时通过ManagerID和TeamLeadID关联到上级，比如T1_1的Manager是M2、TeamLead是T1，递归时会出现M2→T1→T1_1→M2→T1...的循环，直接耗尽递归上限。
2. 结果重复：同一个员工会被多次加入CTE，导致查询结果包含大量重复记录。
3. 性能低下：未针对ManagerID和TeamLeadID创建索引，递归过程中重复扫描数据，生产环境数据量大时性能极差。

最优解决方案 #

1. 先优化索引提升基础性能 #

在关联字段上创建非聚集索引，加速递归时的连接查询：

CREATE NONCLUSTERED INDEX IX_Employee_ManagerID ON dbo.Employee(ManagerID);
CREATE NONCLUSTERED INDEX IX_Employee_TeamLeadID ON dbo.Employee(TeamLeadID);

2. 修正递归逻辑：避免循环与重复 #

在CTE中加入已访问员工ID的追踪，确保每个员工仅被处理一次，彻底解决循环问题：

DECLARE @TargetEmployeeID INT = 1; -- 可替换为目标员工ID

;WITH Hierarchy AS 
(
    -- 初始节点：目标员工，同时记录已访问的ID列表
    SELECT 
        EmployeeID, 
        EmployeeName, 
        ManagerID, 
        TeamLeadID,
        CAST(',' + CAST(EmployeeID AS VARCHAR(MAX)) + ',' AS VARCHAR(MAX)) AS VisitedIDs
    FROM dbo.Employee  
    WHERE EmployeeID = @TargetEmployeeID

    UNION ALL  

    SELECT 
        e.EmployeeID, 
        e.EmployeeName, 
        e.ManagerID, 
        e.TeamLeadID,
        CAST(h.VisitedIDs + CAST(e.EmployeeID AS VARCHAR(MAX)) + ',' AS VARCHAR(MAX)) AS VisitedIDs
    FROM dbo.Employee e 
    INNER JOIN Hierarchy h 
        ON (h.EmployeeID = e.ManagerID OR h.EmployeeID = e.TeamLeadID)
        -- 关键判断：当前员工未被访问过，避免循环和重复
        AND CHARINDEX(',' + CAST(e.EmployeeID AS VARCHAR(MAX)) + ',', h.VisitedIDs) = 0
)  
SELECT DISTINCT
    EmployeeID, 
    EmployeeName, 
    ManagerID, 
    TeamLeadID
FROM Hierarchy
OPTION (MAXRECURSION 10000);

3. 结果验证 #

• 当@TargetEmployeeID=1时，返回全部10条员工记录，符合预期。
• 当@TargetEmployeeID=2时，返回M2、T1、T1_1、T1_2、T1_3共5条记录，符合预期。
• 当@TargetEmployeeID=10时，仅返回T2_2自身记录，符合预期。

4. 进阶优化（超大数据量场景） #

如果员工数量极大，字符串拼接追踪已访问ID的性能会下降，可改用表值类型追踪已访问ID：

-- 先创建表值类型（仅需执行一次）
CREATE TYPE dbo.IntList AS TABLE (ID INT PRIMARY KEY);

DECLARE @TargetEmployeeID INT = 1;
DECLARE @Visited dbo.IntList;
INSERT INTO @Visited VALUES (@TargetEmployeeID);

;WITH Hierarchy AS 
(
    SELECT * FROM dbo.Employee WHERE EmployeeID = @TargetEmployeeID

    UNION ALL  

    SELECT e.*
    FROM dbo.Employee e 
    INNER JOIN Hierarchy h 
        ON (h.EmployeeID = e.ManagerID OR h.EmployeeID = e.TeamLeadID)
    WHERE NOT EXISTS (SELECT 1 FROM @Visited v WHERE v.ID = e.EmployeeID)
    AND EXISTS (
        INSERT INTO @Visited (ID)
        SELECT e.EmployeeID
        WHERE NOT EXISTS (SELECT 1 FROM @Visited v WHERE v.ID = e.EmployeeID)
    )
)  
SELECT * FROM Hierarchy;

内容的提问来源于stack exchange，提问作者Nelson