T-SQL 排序规则的优先顺序

排序规则的优先顺序

排序规则的优先顺序（也称为排序规则类型强制规则）是赋予一组规则的术语，而这组规则决定：

1. 取值为字符串的表达式的最终结果的排序规则。
   
   
2. 区分排序规则的运算符所使用的排序规则，这些运算符使用字符串输入但不返回字符串，如 LIKE 和 IN。

排序规则的优先顺序规则只适用于字符串数据类型：char、varchar、text、nchar、nvarchar 和 ntext。具有其它数据类型的对象不参加排序规则的评估。

所有对象的排序规则可归为四类中的一种。每类的名称叫做排序规则标志。

排序规则标志	对象类型
强制默认	任何 Transact-SQL 字符串变量、参数、字面值、目录内置函数的输出、或不使用字符串输入但产生字符串输出的内置函数。 如果在用户定义函数、存储过程或触发器中声明某个对象，则将该对象指派为在其中创建函数、存储过程或触发器的数据库的默认排序规则。如果在批处理中声明该对象，则将其指派为连接所使用的当前数据库的默认排序规则。
隐性 X	列引用。从为表或视图中的列定义的排序规则得到表达式（由 X 表示）的排序规则。 即使 CREATE TABLE 或 CREATE VIEW 语句中的 COLLATE 子句给列显式指派了排序规则，该列引用仍归为隐性。
显式 X	使用表达式中的 COLLATE 子句显式投影到特定排序规则（由 X 表示）的表达式。
无排序规则	表示表达式的值是两个字符串之间操作的结果，而这两个字符串具有隐性排序规则标志的冲突排序规则。表达式的结果被定义为不具有排序规则。

只引用一个字符串对象的简单表达式的排序规则标志是被引用对象的排序规则标志。

如果复杂表达式所引用的两个操作数表达式的排序规则标志相同，则该复杂表达式的排序规则标志为数表达式的排序规则标志。

如果复杂表达式所引用的两个操作数表达式的排序规则不同，则该复杂表达式最终结果的排序规则标志基于下列规则：

1. 显式优先于隐性。隐性优先于强制默认。换言之，

   显式 > 隐性 > 强制默认

2. 组合两个已指派给不同排序规则的显式表达式将生成错误。

   显式 X + 显式 Y = 错误

3. 组合两个具有不同排序规则的隐性表达式将产生无排序规则。

   隐性 X + 隐性 Y = 无排序规则

4. 将无排序规则的表达式与任何标志的表达式组合，除了显式排序规则外（参见下一项），都产生无排序规则标志的结果。

   无排序规则 + 任何标志 = 无排序规则

5. 将无排序规则的表达式与显式排序规则的表达式组合将产生具有显式标志的表达式。

   无排序规则 + 显式 X = 显式

下面的示例说明上述规则。

USE tempdb
GO

CREATE TABLE TestTab (
   id int, 
   GreekCol nvarchar(10) collate greek_ci_as, 
   LatinCol nvarchar(10) collate latin1_general_cs_as
   )
INSERT TestTab VALUES (1, N'A', N'a')
GO

下面查询中的谓词具有排序规则冲突，因此产生错误：

SELECT * 
FROM TestTab 
WHERE GreekCol = LatinCol

下面是结果集。

Msg 446, Level 16, State 9, Server CTSSERV, Line 1
Cannot resolve collation conflict for equal to operation.

下面查询中的谓词在排序规则 greek_ci_as 中取值，因为右边表达式具有显式标志，优先于右边表达式的隐性标志：

SELECT * 
FROM TestTab 
WHERE GreekCol = LatinCol COLLATE greek_ci_as

下面是结果集。

id          GreekCol             LatinCol
 ----------- -------------------- --------------------
           1 a                    A

(1 row affected)

下面查询中的 case 表达式没有排序规则标志，所以不能出现在选择列表中，也不能由区分排序规则的运算符操作。不过，这些表达式可由不区分排序规则的运算符操作。

SELECT (CASE WHEN id > 10 THEN GreekCol ELSE LatinCol END) 
FROM TestTab

下面是结果集。

Msg 451, Level 16, State 1, Line 1
Cannot resolve collation conflict for column 1 in SELECT statement.

SELECT PATINDEX((CASE WHEN id > 10 THEN GreekCol ELSE LatinCol END), 'a')
FROM TestTab

下面是结果集。

Msg 446, Level 16, State 9, Server LEIH2, Line 1
Cannot resolve collation conflict for patindex operation.

SELECT (CASE WHEN id > 10 THEN GreekCol ELSE LatinCol END) COLLATE Latin1_General_CI_AS 
FROM TestTab

下面是结果集。

--------------------
a

(1 row affected)

下表汇总了上述规则。

操作数强制标志	显式 X	隐性 X	强制默认	无排序规则
显式 Y	生成错误	结果为显式 Y	结果为显式 Y	结果为显式 Y
隐性 Y	结果为显式 X	结果为无排序规则	结果为隐性 Y	结果为无排序规则
强制默认	结果为显式 X	结果为隐性 X	结果为强制默认	结果为无排序规则
无排序规则	结果为显式 X	结果为无排序规则	结果为无排序规则	结果为无排序规则

运算符和函数区分排序规则或不区分排序规则：

1. 区分排序规则表示指定无排序规则操作数是编译时错误。表达式结果不能是无排序规则。
   
   
2. 不区分排序规则表示操作数和结果可以是无排序规则。

比较运算符以及 MAX、MIN、BETWEEN、LIKE 和 IN 运算符都区分排序规则。运算符所使用的字符串被赋以具有较高优先顺序的操作数的排序规则标志。UNION 运算符也区分排序规则，且所有的字符串操作数和最终结果被赋以具有最高优先顺序的操作数的排序规则。按列评估 UNION 操作数和结果的排序规则优先顺序。

赋值运算符不区分排序规则，右边的表达式投影到左边的排序规则上。

字符串串联运算符不区分排序规则，两个字符串操作数和结果被赋以具有最高排序规则优先顺序的操作数的排序规则标志。UNION ALL 和 CASE 运算符不区分排序规则，所有的字符串操作数和最终结果都被赋以具有最高优先顺序的操作数的排序规则标志。按列评估 UNION ALL 操作数和结果的排序规则优先顺序。

CAST、CONVERT 和 COLLATE 函数区分 char、varchar 和 text 数据类型的排序规则。如果 CAST 和 CONVERT 函数的输入和输出是字符串，则输出字符串具有输入字符串的排序规则标志。如果输入不是字符串，则输出字符串为强制默认并被赋以连接所使用的当前数据库的排序规则，或是包含引用 CAST 或 CONVERT 的用户定义函数、存储过程或触发器的数据库的排序规则。

对于返回字符串但不使用字符串输入的内置函数，结果字符串为强制默认并被赋以当前数据库的排序规则，或是包含引用该函数的用户定义函数、存储过程或触发器的数据库的排序规则。

下列函数区分排序规则，其输出字符串具有输入字符串的排序规则标志：

1. CHARINDEX
   
   
2. DIFFERENCE
   
   
3. ISNUMERIC
   
   
4. LEFT
   
   
5. LEN
   
   
6. LOWER
   
   
7. PATINDEX
   
   
8. REPLACE
   
   
9. REVERSE
   
   
10. RIGHT
    
    
11. SOUNDEX
    
    
12. STUFF
    
    
13. SUBSTRING
    
    
14. UPPER

下列附加规则也适用于排序规则优先顺序：

1. 在已经是显式表达式的表达式上不能有多个 COLLATE 子句。例如，下面的 WHERE 子句是不合法的，因为将 COLLATE 子句指定给的表达式已经是显式表达式：

   WHERE ColumnA = ( 'abc' COLLATE French_CI_AS) COLLATE French_CS_AS
   

2. text 数据类型不允许代码页转换。如果排序规则的代码页不同，不能将文本表达式从一种排序规则投影到另一种排序规则。如果右边文本操作数的排序规则代码页与左边文本操作数不同，则赋值运算符不能赋值。

在数据类型转换后确定排序规则的优先顺序。产生结果排序规则的操作数可以与提供最终结果数据类型的操作数不同。例如，考察下面的批处理：

CREATE TABLE TestTab
   (PrimaryKey int PRIMARY KEY,
    CharCol char(10) COLLATE French_CI_AS
   )

SELECT *
FROM TestTab
WHERE CharCol LIKE N'abc'

简单表达式 N'abc' 的 Unicode 数据类型具有较高的数据类型优先顺序，所以结果表达式将 Unicode 数据类型指派给 N'abc'。然而，表达式 CharCol 具有隐性排序规则标志，而 N'abc' 具有较低的强制默认排序规则标志，所以使用的排序规则是 CharCol 的 French_CI_AS 排序规则。