为何在子查询中结合FOR XML PATH时Collate排序规则不生效？

这个问题的核心原因和FOR XML PATH的输出特性、SQL Server的类型转换逻辑以及排序规则的优先级密切相关，我来一步步拆解：

1. 普通子查询的工作原理 #

你之前用的COLLATE SQL_Latin1_General_CP1251_CI_AS能去除重音，是因为这个排序规则基于CP1251单字节代码页——在这个编码里，带重音的字符（比如ě、š）会被映射成对应的无重音字符（e、s）。当你给整个拼接表达式加上这个COLLATE时，SQL Server会把整个字符串转换为该排序规则对应的VARCHAR类型，从而自动去除重音。

普通子查询返回的结果和外层字符串的类型、排序规则一致，所以整个拼接表达式能统一应用指定的COLLATE，效果正常。

2. FOR XML PATH的特殊输出 #

当你在子查询里加上FOR XML PATH('')时，情况就变了：

• FOR XML会强制将子查询的输出转换为**NVARCHAR(MAX)**类型（Unicode编码），而不是普通子查询的VARCHAR（或原字符串类型）。
• 这个NVARCHAR结果的排序规则是数据库默认的Unicode排序规则（比如Latin1_General_CI_AS），而非你期望的SQL_Latin1_General_CP1251_CI_AS。

3. 类型转换与排序规则优先级 #

SQL Server处理字符串拼接时，遵循严格的类型优先级：NVARCHAR的优先级高于VARCHAR。当你把一个VARCHAR字符串和NVARCHAR字符串拼接时，SQL Server会自动把VARCHAR转换为NVARCHAR，整个拼接结果也会变成NVARCHAR类型。

而SQL_Latin1_General_CP1251_CI_AS是针对单字节VARCHAR的排序规则，它的重音映射逻辑对Unicode的NVARCHAR完全不起作用——因为Unicode中带重音的字符是独立的编码，不会被自动映射为无重音字符。即使你在整个表达式末尾加COLLATE，也无法改变结果是NVARCHAR的事实，自然就无法去除重音。

验证一下这个逻辑 #

你可以运行下面的代码，对比两种子查询的输出类型和排序规则：

-- 普通子查询的类型与排序规则
SELECT 
  SQL_VARIANT_PROPERTY((SELECT 'ěščřžýáíé'), 'BaseType') AS NormalSubqueryType,
  SQL_VARIANT_PROPERTY((SELECT 'ěščřžýáíé'), 'Collation') AS NormalSubqueryCollation;

-- 带FOR XML PATH的子查询的类型与排序规则
SELECT 
  SQL_VARIANT_PROPERTY((SELECT 'ěščřžýáíé' FOR XML PATH('')), 'BaseType') AS XMLSubqueryType,
  SQL_VARIANT_PROPERTY((SELECT 'ěščřžýáíé' FOR XML PATH('')), 'Collation') AS XMLSubqueryCollation;

你会发现，普通子查询返回的是VARCHAR（或与原字符串一致的类型），而XML子查询返回的是NVARCHAR(MAX)，排序规则也变成了数据库默认的Unicode规则。

为什么单独给各部分加COLLATE有效？ #

当你给子查询的结果也加上COLLATE SQL_Latin1_General_CP1251_CI_AS时，相当于强制把NVARCHAR的子查询结果转换为该排序规则的VARCHAR类型，这样拼接的两个部分都是同类型、同排序规则的VARCHAR，整个表达式就能正常应用重音去除的逻辑了。

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