在此查询的查询计划中，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包含约35万条记录，并且按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应用于聚合源，以及是否可以通过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