排序规则优先级Collation Precedence

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

排序规则优先顺序也称为排序规则强制规则,用于确定:Collation precedence, also known as collation coercion rules, determines the following:

  • 计算结果为字符串的表达式的最终结果排序规则。The collation of the final result of an expression that is evaluated to a character string.

  • 区分排序规则的运算符所使用的排序规则,这些运算符使用字符串输入但不返回字符串,如 LIKE 和 IN。The collation that is used by collation-sensitive operators that use character string inputs but do not return a character string, such as LIKE and IN.

排序规则的优先顺序规则只应用于下列字符串数据类型:char、varchar、text、nchar、nvarchar 和 ntext。The collation precedence rules apply only to the character string data types: char, varchar, text, nchar, nvarchar, and ntext. 具有其他数据类型的对象不参与排序规则计算。Objects that have other data types do not participate in collation evaluations.

排序规则标签Collation Labels

下表列出并说明了四个用于标识所有对象的排序规则的类别。The following table lists and describes the four categories in which the collations of all objects are identified. 每个类别的名称叫做排序规则标签。The name of each category is called the collation label.

排序规则标签Collation label 对象类型Types of objects
强制默认Coercible-default 任何 Transact-SQLTransact-SQL 字符串变量、参数、文字、目录内置函数的输出或不使用字符串输入但生成字符串输出的内置函数。Any Transact-SQLTransact-SQL character string variable, parameter, literal, or the output of a catalog built-in function, or a built-in function that does not take string inputs but produces a string output.

如果在用户定义函数、存储过程或触发器中声明对象,则为该对象分配创建函数、存储过程或触发器所采用的数据库默认排序规则。If the object is declared in a user-defined function, stored procedure, or trigger, the object is assigned the default collation of the database in which the function, stored procedure, or trigger is created. 如果在批处理中声明对象,则为该对象分配用于连接的当前数据库的默认排序规则。If the object is declared in a batch, the object is assigned the default collation of the current database for the connection.
隐式 XImplicit X 列引用。A column reference. 从为表或视图中的列定义的排序规则得到表达式 (X) 的排序规则。The collation of the expression (X) is taken from the collation defined for the column in the table or view.

即使使用 CREATE TABLE 或 CREATE VIEW 语句中的 COLLATE 子句为列显式分配了排序规则,该列引用仍归为隐式。Even if the column was explicitly assigned a collation by using a COLLATE clause in the CREATE TABLE or CREATE VIEW statement, the column reference is classified as implicit.
显式 XExplicit X 使用表达式中的 COLLATE 子句显式转换为特定排序规则 (X) 的表达式。An expression that is explicitly cast to a specific collation (X) by using a COLLATE clause in the expression.
无排序规则No-collation 指示表达式的值是两个字符串之间的运算结果,而这两个字符串具有隐式排序规则标签的冲突排序规则。Indicates that the value of an expression is the result of an operation between two strings that have conflicting collations of the implicit collation label. 表达式的结果被定义为不具有排序规则。The expression result is defined as not having a collation.

排序规则Collation Rules

只引用一个字符串对象的简单表达式的排序规则标签是被引用对象的排序规则标签。The collation label of a simple expression that references only one character string object is the collation label of the referenced object.

如果复杂表达式被引用两个操作数表达式的排序规则标签相同,则该复杂表达式的排序规则标签为操作数表达式的排序规则标签。The collation label of a complex expression that references two operand expressions with the same collation label is the collation label of the operand expressions.

如果复杂表达式被引用两个操作数表达式的排序规则不同,则该复杂表达式最终结果的排序规则标签基于下列规则:The collation label of the final result of a complex expression that references two operand expressions with different collations is based on the following rules:

  • 显式优先于隐式。Explicit takes precedence over implicit. 隐式优先于强制默认:Implicit takes precedence over Coercible-default:

    显式 > 隐式 > 强制默认Explicit > Implicit > Coercible-default

  • 组合两个已被分配有不同排序规则的显式表达式将生成错误:Combining two Explicit expressions that have been assigned different collations generates an error:

    显式 X + 显式 Y = 错误Explicit X + Explicit Y = Error

  • 组合两个具有不同排序规则的隐式表达式将生成无排序规则的结果:Combining two Implicit expressions that have different collations yields a result of No-collation:

    隐式 X + 隐式 Y = 无排序规则Implicit X + Implicit Y = No-collation

  • 将无排序规则的表达式与除显式排序规则(参阅下一个规则)之外任何标签表达式组合都将生成无排序规则标签的结果:Combining an expression with No-collation with an expression of any label, except Explicit collation (see the following rule), yields a result that has the No-collation label:

    无排序规则 + 任何内容 = 无排序规则No-collation + anything = No-collation

  • 将无排序规则的表达式与具有显式排序规则的表达式组合将生成具有显式标签的表达式:Combining an expression with No-collation with an expression that has an Explicit collation, yields an expression with an Explicit label:

    无排序规则 + 显式 X = 显式No-collation + Explicit X = Explicit

下表概述了这些规则。The following table summarizes the rules.

操作数强制标签Operand coercion label 显式 XExplicit X 隐式 XImplicit X 强制默认Coercible-default 无排序规则No-collation
显式 YExplicit Y 生成错误Generates Error 结果为显式 YResult is Explicit Y 结果为显式 YResult is Explicit Y 结果为显式 YResult is Explicit Y
隐式 YImplicit Y 结果为显式 XResult is Explicit X 结果为无排序规则Result is No-collation 结果为隐式 YResult is Implicit Y 结果为无排序规则Result is No-collation
强制默认Coercible-default 结果为显式 XResult is Explicit X 结果为隐式 XResult is Implicit X 结果为强制默认Result is Coercible-default 结果为无排序规则Result is No-collation
无排序规则No-collation 结果为显式 XResult is Explicit X 结果为无排序规则Result is No-collation 结果为无排序规则Result is No-collation 结果为无排序规则Result is No-collation

下列附加规则也适用于排序规则优先顺序:The following additional rules also apply to collation precedence:

  • 在已经是显式表达式的表达式上不能有多个 COLLATE 子句。You cannot have multiple COLLATE clauses on an expression that is already an explicit expression. 例如,下面的 WHERE 子句无效,因为已经为显式表达式指定了 COLLATE 子句:For example, the following WHERE clause is not valid because a COLLATE clause is specified for an expression that is already an explicit expression:

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

  • 不允许进行 text 数据类型的代码页转换。Code page conversions for text data types are not allowed. 如果排序规则的代码页不同,则不能将 text 表达式从一种排序规则转换为另一种排序规则。You cannot cast a text expression from one collation to another if they have the different code pages. 如果右边文本操作数的排序规则代码页与左边文本操作数的排序规则代码页不同,则不能为赋值运算符赋值。The assignment operator cannot assign values when the collation of the right text operand has a different code page than the left text operand.

在数据类型转换之后确定排序规则优先顺序。Collation precedence is determined after data type conversion. 生成结果排序规则的操作数可以与提供最终结果数据类型的操作数不同。The operand from which the resulting collation is taken can be different from the operand that supplies the data type of the final result. 例如,请看下面的批处理:For example, consider the following batch:

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 数据类型有更高的数据类型优先级。The Unicode data type of the simple expression N'abc' has a higher data type precedence. 因此,所生成的表达式将 Unicode 数据类型分配给 N'abc'Therefore, the resulting expression has the Unicode data type assigned to N'abc'. 但是,表达式 CharCol 具有隐式排序规则标签,而 N'abc' 具有级别更低的强制标签,即强制默认。However, the expression CharCol has a collation label of Implicit, and N'abc' has a lower coercion label of Coercible-default. 因此,所使用的排序规则是 French_CI_ASCharCol 排序规则。Therefore, the collation that is used is the French_CI_AS collation of CharCol.

排序规则示例Examples of Collation Rules

以下示例显示排序规则如何工作。The following examples show how the collation rules work. 若要运行该示例,请创建以下测试表。To run the examples, create the following test table.

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  

排序规则冲突和错误Collation Conflict and Error

下面查询中的谓词具有排序规则冲突,因此会产生错误。The predicate in the following query has collation conflict and generates an error.

SELECT *   
FROM TestTab   
WHERE GreekCol = LatinCol;  

下面是结果集:Here is the result set.

Msg 448, Level 16, State 9, Line 2  
Cannot resolve collation conflict between 'Latin1_General_CS_AS' and 'Greek_CI_AS' in equal to operation.  

显式标签与隐式标签Explicit Label vs. Implicit Label

由于右表达式有显式标签,因此采用排序规则 greek_ci_as 计算以下查询中的谓词。The predicate in the following query is evaluated in collation greek_ci_as because the right expression has the Explicit label. 它的优先级高于左表达式的隐式标签。This takes precedence over the Implicit label of the left expression.

SELECT *   
FROM TestTab   
WHERE GreekCol = LatinCol COLLATE greek_ci_as;  

下面是结果集:Here is the result set.

id          GreekCol             LatinCol  
----------- -------------------- --------------------  
          1 A                    a  
  
(1 row affected)  

无排序规则标签No-Collation Labels

下列查询中的 CASE 表达式具有无排序规则标签,所以它们不能出现在选择列表中,也不能由区分排序规则的运算符进行运算。The CASE expressions in the following queries have a No-collation label; therefore, they cannot appear in the select list or be operated on by collation-sensitive operators. 不过,这些表达式可由不区分排序规则的运算符进行运算。However, the expressions can be operated on by collation-insensitive operators.

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

下面是结果集:Here is the result set.

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;  

下面是结果集:Here is the result set.

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;  

下面是结果集:Here is the result set.

--------------------  
a  
  
(1 row affected)  

区分排序规则与不区分排序规则Collation Sensitive and Collation Insensitive

运算符和函数可以区分排序规则,也可以不区分排序规则。Operators and functions are either collation sensitive or insensitive.

区分排序规则Collation sensitive
这表示指定无排序规则操作数是一个编译时错误。This means that specifying a No-collation operand is a compile-time error. 表达式结果不能无排序规则。The expression result cannot be No-collation.

不区分排序规则Collation insensitive
这表示操作数和结果可以无排序规则。This means that the operands and result can be No-collation.

运算符和排序规则Operators and Collation

比较运算符以及 MAX、MIN、BETWEEN、LIKE 和 IN 运算符都区分排序规则。The comparison operators, and the MAX, MIN, BETWEEN, LIKE, and IN operators, are collation sensitive. 运算符所使用的字符串被赋以具有较高优先顺序的操作数的排序规则标签。The string used by the operators is assigned the collation label of the operand that has the higher precedence. UNION 语句也区分排序规则,且所有字符串操作数和最终结果被赋以具有最高优先顺序的操作数的排序规则。The UNION statement is also collation sensitive, and all string operands and the final result is assigned the collation of the operand with the highest precedence. 按列评估 UNION 操作数和结果的排序规则优先顺序。The collation precedence of the UNION operand and result are evaluated column by column.

赋值运算符不区分排序规则,右边的表达式转换到左边的排序规则上。The assignment operator is collation insensitive and the right expression is cast to the left collation.

字符串串联运算符区分排序规则,两个字符串操作数和结果被赋以排序规则优先级最高的操作数的排序规则标签。The string concatenation operator is collation sensitive, the two string operands and the result are assigned the collation label of the operand with the highest collation precedence. UNION ALL 和 CASE 语句不区分排序规则,所有的字符串操作数和最终结果都被赋以具有最高优先顺序的操作数的排序规则标签。The UNION ALL and CASE statements are collation insensitive, and all string operands and the final results are assigned the collation label of the operand with the highest precedence. 按列评估 UNION ALL 操作数和结果的排序规则优先顺序。The collation precedence of the UNION ALL operands and result are evaluated column by column.

函数和排序规则Functions and Collation

对于 char、varchar 和 text 数据类型,CAST、CONVERT 和 COLLATE 函数区分排序规则。THE CAST, CONVERT, and COLLATE functions are collation sensitive for char, varchar, and text data types. 如果 CAST 和 CONVERT 函数的输入和输出是字符串,则输出字符串具有输入字符串的排序规则标签。If the input and output of the CAST and CONVERT functions are character strings, the output string has the collation label of the input string. 如果输入不是字符串,则输出字符串为强制默认并被赋以连接所使用的当前数据库的排序规则,或是包含引用 CAST 或 CONVERT 的用户定义函数、存储过程或触发器的数据库的排序规则。If the input is not a character string, the output string is Coercible-default and assigned the collation of the current database for the connection, or the database that contains the user-defined function, stored procedure, or trigger in which the CAST or CONVERT is referenced.

对于返回字符串但不使用字符串输入的内置函数,结果字符串为强制默认并被赋以当前数据库的排序规则,或是包含引用该函数的用户定义函数、存储过程或触发器的数据库的排序规则。For the built-in functions that return a string but do not take a string input, the result string is Coercible-default and is assigned either the collation of the current database, or the collation of the database that contains the user-defined function, stored procedure, or trigger in which the function is referenced.

下列函数区分排序规则,并且它们的输出字符串具有输入字符串的排序规则标签:The following functions are collation-sensitive and their output strings have the collation label of the input string:

CHARINDEXCHARINDEX

REPLACEREPLACE

DIFFERENCEDIFFERENCE

REVERSEREVERSE

ISNUMERICISNUMERIC

RIGHTRIGHT

LEFTLEFT

SOUNDEXSOUNDEX

LENLEN

STUFFSTUFF

LOWERLOWER

SUBSTRINGSUBSTRING

PATINDEXPATINDEX

UPPERUPPER

另请参阅See Also

COLLATE (Transact-SQL) COLLATE (Transact-SQL)
数据类型转换(数据库引擎) Data Type Conversion (Database Engine)
运算符 (Transact-SQL) Operators (Transact-SQL)
表达式 (Transact-SQL)Expressions (Transact-SQL)