Collate in SQL Server

What is Collate?

Collate is a set of rules that tell how to compare and sort strings: letters order; whether case matters, whether diacritics MATTER etc.

Normally we have 4 type of sensitivity on SQL Server (Case, Width, Accent, Kanatype)

When we create new table in existing database and doesn’t specify the collate along with column name then SQL uses the default collate specified in the system at the time of installation or which is part of SQL server setting for those fields.

When we use these newly created tables fields in comparision operator with other tables fields then we encounter SQL error related to collate for example:
Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AI” in the equal to operation.

Where “Latin1_General_CI_AI” means source table field using “Latin1_General_CI_AI” as collate and then one which is being compared using “SQL_Latin1_General_CP1_CI_AS” as collate.

These can be resolved using the below options

Collate in Select
Syntax: Select ColunmName collate SQL_Latin1_General_CP1_CI_AS As ColunmName

Collate in Where Clause
Where ColunmName collate SQL_Latin1_General_CP1_CI_AS

Alter Table for Collate
Alter table TableName Alter ColumnName varchar(100) collate SQL_Latin1_General_CP1_CI_AS

Collate in ON clause of Join
On (Table1.ColumnName collate SQL_Latin1_General_CP1_CI_AS = Table2.ColumnName collate SQL_Latin1_General_CP1_CI_AS)

Advertisements

#collate