Collation Precedence
Collation precedence, also known as collation coercion rules, is the term given to the set of rules that determine:
- The collation of the final result of an expression that is evaluated to a character string.
- The collation used by collation-sensitive operators that use character string inputs but do not return a character string, such as LIKE and IN.
The collation precedence rules apply only to the character string data types, char, varchar, text, nchar, nvarchar, and ntext. Objects with other data types do not participate in collation evaluations.
The collation of all objects falls into one of four categories. The name of each category is called the collation label.
| Collation label | Types of objects |
| Coercible-default | Any Transact-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, it 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, it is assigned the default collation of the current database for the connection. |
| Implicit X | A column reference. The collation of the expression (denoted by X) is taken from the collation defined for the column in the table or view.
Even if the column was explicitly assigned a collation by a COLLATE clause in the CREATE TABLE or CREATE VIEW statement, the column reference is classified as implicit. |
| Explicit X | An expression that is explicitly cast to a specific collation (denoted by X) 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 with conflicting collations of the implicit collation label. The expression result is defined as not having a collation. |
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 these rules:
- Explicit takes precedence over implicit. Implicit takes precedence over coercible-default. In other words,
Explicit > Implicit > Coercible-Default
- Combining two explicit expressions that have been assigned different collations generates an error.
Explicit X + Explicit Y = Error
- Combining two implicit expressions that have different collations yields a result of no-collation.
Implicit X + Implicit Y = No-collation
- Combining an expression with no-collation with an expression of any label, except explicit collation (see following bullet), 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.
No-collation + Explicit X = Explicit
These examples illustrate the rules.
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
The predicate in the following query has collation conflict and generates an error:
SELECT *
FROM TestTab
WHERE GreekCol = LatinCol
This is the result set.
Msg 446, Level 16, State 9, Server CTSSERV, Line 1
Cannot resolve collation conflict for equal to operation.
The predicate in the following query is evaluated in collation greek_ci_as because the right expression has the explicit label, which takes precedence over the implicit label of the right expression:
SELECT *
FROM TestTab
WHERE GreekCol = LatinCol COLLATE greek_ci_as
This is the result set.
id GreekCol LatinCol
----------- -------------------- --------------------
1 a A
(1 row affected)
The case expressions in the following queries have no collation label so they cannot appear in the select list or be operated 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)
This table summarizes the rules.
| Operand coercion label | Explicit X |
Implicit X |
Coercible-default | No-collation |
| Explicit Y | Generates Error | Result is Explicit Y | Result is Explicit Y | Result is Explicit Y |
| Implicit Y | Result is Explicit X | Result is No-collation | Result is Implicit Y | Result is No-collation |
| Coercible-default | Result is Explicit X | Result is Implicit X | Result is Coercible-default | Result is No-collation |
| No-collation | Result is Explicit X | Result is No-collation | Result is No-collation | Result is No-collation |
Operators and functions are either collation sensitive or insensitive:
- Collation sensitive means that specifying a no-collation operand is a compile-time error. The expression result cannot be no-collation.
- Collation insensitive means that the operands and result can be no-collation.
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. The UNION operator is also collation sensitive, and all string operands and the final result is assigned the collation of the operand with the highest precedence. The collation precedence of the UNION operands 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 insensitive, the two string operands and the result are assigned the collation label of the operand with the highest collation precedence. The UNION ALL and CASE operators are collation insensitive, and all string operands and the final results are assigned the collation label of the operand with the highest precedence. The collation precedence of the UNION ALL operands and result are evaluated column by column.
THE CAST, CONVERT, and COLLATE functions are collation sensitive for char, varchar, and text data types. 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. 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 containing 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 containing the user-defined function, stored procedure, or trigger in which the function is referenced.
These functions are collation-sensitive and their output strings have the collation label of the input string:
- CHARINDEX
- DIFFERENCE
- ISNUMERIC
- LEFT
- LEN
- LOWER
- PATINDEX
- REPLACE
- REVERSE
- RIGHT
- SOUNDEX
- STUFF
- SUBSTRING
- UPPER
These additional rules also apply to collation precedence:
- You cannot have multiple COLLATE clauses on an expression that is already an explicit expression. For example, this WHERE clause is illegal 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 - Code page conversions for text data types are not allowed. You cannot cast a text expression from one collation to another if they have the different code pages. The assignment operator cannot assign values if the collation of the right text operand has a different code page than the left text operand.
Determination of collation precedence takes place 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 this batch:
CREATE TABLE TestTab
(PrimaryKey int PRIMARY KEY,
CharCol char(10) COLLATE French_CI_AS
)
SELECT *
FROM TestTab
WHERE CharCol LIKE N'abc'
The Unicode data type of the simple expression N'abc' has a higher data type precedence, so the resulting expression has the Unicode data type assigned to N'abc'. The expression CharCol, however, has a collation label of Implicit, while N'abc' has a lower coercion label of coercible-default, so the collation used is the French_CI_AS collation of CharCol.