递归查询慢主因是执行计划中nested loops导致指数级回表扫描，需在parent_id和id列建索引、设maxrecursion上限、锚点过滤根节点。

为什么递归查询慢？先看执行计划里的 Nested Loops

SQL Server 中用 WITH + 递归 CTE 查询树形结构（比如部门上下级、BOM 物料清单）时，如果没加限制或索引支持，执行计划里大概率出现深嵌套的 Nested Loops 操作——这意味着每层子节点都要回表扫描父节点匹配，数据量稍大就指数级膨胀。不是 CTE 本身慢，是默认没剪枝、没走索引。

实操建议：

• 必须在递归锚点（anchor member）和递归成员（recursive member）的 JOIN 条件列上建索引，例如 parent_id 和 id 都要有单独或组合索引
• 用 OPTION (MAXRECURSION n) 显式设上限，避免无限循环拖垮服务器；不写则默认 100 层，超限报错 Msg 530
• 递归前先过滤根节点，别把全表当起点，比如 WHERE id = @root_id 要写在锚点查询里，而不是递归后 WHERE

CTE 递归体里不能用哪些东西？常见语法雷区

递归 CTE 的递归成员部分受严格限制，违反会直接报错 Msg 316 或 Msg 467。不是所有 T-SQL 语法都能放进去。

实操建议：

• 禁止使用聚合函数（SUM、COUNT）、窗口函数（ROW_NUMBER()）、GROUP BY、HAVING、ORDER BY（除非配合 TOP）
• 不能引用外部表——所有数据源必须来自 CTE 自身或锚点查出的表；想关联额外信息，得在 CTE 外层 SELECT 时 JOIN
• UNION ALL 是强制的，UNION 会去重并导致无法递归（报错 Msg 252）

怎么让递归结果按树形顺序排？ORDER BY 不起作用怎么办

CTE 本身不保证输出顺序，即使你在最后一层 SELECT 加了 ORDER BY，SQL Server 也可能因并行执行打乱层级顺序。要真按“根→子→孙”深度优先或广度优先排列，得靠排序字段。

实操建议：

• 在递归 CTE 内部生成路径字符串：锚点设 sort_path = CAST(id AS VARCHAR(800))，递归部分拼接 sort_path = t.sort_path + '/' + CAST(r.id AS VARCHAR(10))，最后按该字段 ORDER BY sort_path
• 若只关心层级深度，加个 level INT 列：锚点设 1，递归部分 t.level + 1，再用 ORDER BY level, id
• 避免在 CTE 里用 ORDER BY（语法允许但无效），排序逻辑一律放到最外层查询

大数据量下递归 CTE 还卡？试试用临时表+循环替代

当树深度不大但宽度过高（比如一个父节点有上万子节点），CTE 仍可能内存占用大、编译时间长。这时传统 WHILE 循环 + #temp 表反而更可控。

实操建议：

• 先用 SELECT ... INTO #stack 初始化根节点；然后 WHILE @@ROWCOUNT > 0 循环插入下一层，每次只处理当前层子节点
• 给临时表建索引：CREATE INDEX IX_#stack_id ON #stack(id)，加速下一轮 JOIN
• 注意清理：循环结束前 DROP TABLE #stack，否则多次执行会报 object already exists

递归 CTE 简洁，但不是银弹；真正压测过几百万节点的树，你会发现路径字段维护和分层批处理比纯 CTE 更稳。别迷信语法糖，索引、剪枝、分步才是关键。