为什么查询计划中存在重复的 CTE 计算以及如何在不重复代码的情况下对其进行优化？

在该查询的查询计划中，grp_set 的计算重复了 4 次（不同排序每次占用 23%，因此占用了所有资源的 23 * 4 = 92%）：

with
     grp_set as (select distinct old_num,old_tbl,old_db,old_val_num from err_calc)
    ,grp as (select id = row_number() over (order by old_num),* from grp_set)

    ,leaf as (select grp.id ,c.* ,sort = convert(varchar(max),old_col) + " - " + severity + " - " + err
        from grp
        join err_calc c on
                            c.old_num   = grp.old_num
                        and c.old_tbl       = grp.old_tbl
                        and c.old_db        = grp.old_db
                        and c.old_val_num   = grp.old_val_num
    )

    select old_num,old_tbl,old_db,old_val_num,conc.*
        from (select sep=",") sep
        cross join grp
        cross apply (select
             old_col    = stuff((select sep + old_col   from leaf where leaf.id = grp.id order by leaf.sort FOR XML PATH("")),1,len(sep),"")
            ,old_val    = stuff((select sep + old_val   from leaf where leaf.id = grp.id order by leaf.sort FOR XML PATH("")),1,len(sep),"")
            ,severity   = stuff((select sep + severity  from leaf where leaf.id = grp.id order by leaf.sort FOR XML PATH("")),1,len(sep),"")
            ,err        = stuff((select sep + err       from leaf where leaf.id = grp.id order by leaf.sort FOR XML PATH("")),1,len(sep),"")
        ) conc

表err_calc包含大约350K记录，并且只有一个索引：old_db,old_tbl,new_tbl,severity,err,old_col,new_col,old_val_num,old_val,old_num,new_num。

由于 SQL 中缺乏串联聚合，此查询的目的是串联每组 4 个字符串字段。

如果串联聚合存在或使用 CLR 实现、是否可以将 order by 应用于聚合源以及是否可以引用所有分组字段，则为等效且所需的查询grouping.*将会：

select grouping.*
    ,severity   =conc(sep+severity)
    ,err        =conc(sep+err)
    ,old_col    =conc(sep+old_col)
    ,old_val    =conc(sep+old_val)
    from err_calc
    cross join (select sep=',') sep
    group by old_num,old_tbl,old_db,old_val_num
    order by old_col,severity,err

因为它的使用方式就像子查询一样，并且使用了多次。参见在同一查询中多次调用 CTE https://stackoverflow.com/questions/18094760/calling-cte-multiple-times-in-same-query

你应该用一个重写你的查询JOIN用你的 CTE 而不是CROSS APPLY，并将字符串连接的逻辑放在SELECT查询的一部分，那么 CTE 将被调用一次。