为何该场景下TSQL的string_agg无法修改分隔符？

问题现象 #

在使用STRING_AGG聚合函数时，当两个STRING_AGG调用的第一个参数为完全相同的子表达式（如substring(mType,1,3)），即使指定不同分隔符，第二个字段的分隔符会失效，复用第一个的分隔符；但修改子表达式参数（如将substring长度从3改为4）后，分隔符就能正常生效。

对应的异常查询：

select string_agg( substring( mType,1,3 ), ',' )  mType
      ,string_agg( substring( mType,1,3), '-' )  mType2
from ourDB..mTypeTable
where ref = '3944900'

修改子表达式后正常的查询：

select string_agg( substring( mType,1,3 ), ',' )  mType
      ,string_agg( substring( mType,1,4), '-' )  mType2
from ourDB..mTypeTable
where ref = '3944900'

根本原因 #

这是SQL Server查询优化器的公共子表达式消除（Common Subexpression Elimination, CSE） 机制导致的。优化器会自动识别查询中完全一致的子表达式，仅计算一次并复用结果，以此减少重复计算、提升查询性能。在异常场景中，两个STRING_AGG的输入子表达式完全相同，优化器将其判定为同一个计算单元，因此第二个STRING_AGG的分隔符参数被忽略，直接复用第一个聚合后的带逗号分隔的结果。

解决方案 #

只需打破两个子表达式的一致性，让优化器无法判定为公共子表达式即可，比如：

• 给其中一个子表达式添加无意义的字符串拼接（如substring(mType,1,3) + ''）
• 用等价但写法不同的函数替代（如left(mType,3)替换其中一个substring(mType,1,3)）

修正后的示例查询：

select string_agg( substring( mType,1,3 ), ',' )  mType
      ,string_agg( substring( mType,1,3) + '', '-' )  mType2
from ourDB..mTypeTable
where ref = '3944900'

补充说明 #

公共子表达式消除是SQL Server默认的性能优化策略，多数场景下能提升查询效率，但当需要对相同基础表达式执行不同聚合操作时，就需要手动打破子表达式的一致性，强制优化器分别处理两个聚合逻辑。

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