定序優先順序Collation Precedence

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel 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.

定序優先順序規則只適用於字元字串資料類型:charvarchartextncharnvarcharntextThe 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

CAST、CONVERT,和 COLLATE 函式會針對 charvarchartext 資料類型區分定序。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)