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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s