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.

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:
  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 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:
  No-collation + Explicit X = Explicit
  

The following table summarizes the rules.

The following additional rules also apply to collation precedence:

• You cannot have multiple COLLATE clauses on an expression that is already an explicit expression. 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
  
• 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 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'

The Unicode data type of the simple expression N'abc' has a higher data type precedence. Therefore, the resulting expression has the Unicode data type assigned to N'abc'. However, the expression CharCol has a collation label of Implicit, and N'abc' has a lower coercion label of Coercible-default. Therefore, the collation that is used is the French_CI_AS collation of CharCol.

Examples of Collation 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

SELECT * 
FROM TestTab 
WHERE GreekCol = LatinCol;

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.

SELECT * 
FROM TestTab 
WHERE GreekCol = LatinCol COLLATE greek_ci_as;

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

(1 row affected)

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)

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

Functions and Collation

Reference

Other Resources

Help and Information