COLLATE Clause

is a clause that can be applied when defining a varchar/char column in a CREATE TABLE statement.

This clause can also be applied to a varchar/char operand or result.

Syntax

COLLATE <collation>

Argument

<collation>

is a valid collation name. E.g. latin1_general_ci_as, en_ci_as, fr_ci_as.

Remarks

You cannot apply a COLLATE clause to a variable.

DECLARE @a VARCHAR(30) COLLATE en_cs_as   -- invalid syntax

raises a syntax error.

Examples

-- default server collation is en_ci_ai
PRINT 'hello' = 'HELLO';

-- collation used for comparison is english, case-sensitive, accent-sensitive
PRINT 'hello' = 'HELLO' COLLATE en_cs_as;

-- collation used for comparison is english, case-sensitive, accent-sensitive
PRINT 'hello' COLLATE en_cs_as = 'HELLO';

The result is:

true
false
false

Comparison operators like = depend on the collations of the operands, and use the “strongest” collation to perform the actual comparison.

The collation precedence is, from “strongest” to “weakest”:

1. collation specified by COLLATE clause applied to an operand.
2. collation specified by COLLATE clause in the column definition of CREATE TABLE.
3. default server collation.

Example of Collation Conflict

To avoid collation conflicts, you should use the COLLATE clause sparingly.

PRINT 'hello' COLLATE en_cs_as = 'HELLO' COLLATE en_ci_ai;

The result is:

<Syntax error. Cannot resolve conflict between en_ci_ai and en_cs_as collations.>

Example of collation inheritance

In the first example below, the first operand of CHARINDEX has the server default collation en_ci_ai.

In the second example below, this operand has en_cs_as collation, inherited from the COLLATE clause, higher in the parse tree.

PRINT CHARINDEX('a' + CAST(123 as VARCHAR(100)) + 'b', 'hello A123B apple a123b banana');
PRINT CHARINDEX('a' + CAST(123 as VARCHAR(100)) COLLATE en_cs_as + 'b', 'hello A123B apple a123b banana');

The result is:

7
19