Collate in SQL Server

Collate specifies the set of rules that are used during various comparisons, sorting etc. For example it specifies whether the comparisons should be case sensitive or not etc.


Let's take a look at how different types of collate works. Let the data in our table be:-
FirstnameLastname
JillSmith
EveJackson
jillWilliam
JillJackson
eveSmith
johnDoe

  • for the query :- SELECT * FROM T1 ORDER BY Firstname COLLATE SQL_Latin1_General_CP1_CS_AS the result is
FirstnameLastname
eveSmith
EveJackson
jillWilliam
JillJackson
JillSmith
johnDoe

The CS in "SQL_Latin1_General_CP1_CS_AS" stands for case sensitivity but the case matters only if there is a conflict otherwise it sorts in normal alphabetical order. But the real difference is noticed in case of comparison for example the result of query:-SELECT * FROM T1 where Firstname = 'jill' COLLATE SQL_Latin1_General_CP1_CS_AS is

FirstnameLastname
jillWilliam

As you can clearly notice the above comparison is case sensitive, that's why only one record is selected
  • for the query :- SELECT * FROM T1 ORDER BY Firstname COLLATE SQL_Latin1_General_CP1_CI_AS the result is
FirstnameLastname
EveJackson
eveSmith
jillWilliam
JillJackson
JillSmith
johnDoe

As you can see the sorting is irrespective of case and moreover the result of query:-SELECT * FROM T1 where Firstname = 'jill' COLLATE SQL_Latin1_General_CP1_CI_AS is

FirstnameLastname
JillSmith
jillWilliam
JillJackson

In the above query the collation is case insensitive that is why "jill" and "Jill" are treated as same
  • To get the sorting in actual case sensitive manner you need to use binary collate, for example the result of query:- SELECT * FROM T1 order by Firstname COLLATE Latin1_General_bin is
FirstnameLastname
EveJackson
JillJackson
JillSmith
eveSmith
jillWilliam
johnDoe


Different sections in collate "SQL_Latin1_General_CP1_CI_AS" are explained as below:-
  1. latin1 makes the server treat strings using charset latin 1, basically ascii
  2. CI case insensitive comparisons so 'ABC' would equal 'abc'
  3. AS accent sensitive, so 'ΓΌ' does not equal 'u

You can get the collation of SQL Server using the query:- select serverproperty('collation')

To get collation of a specific database use the query :- select databasepropertyex('DIVA','collation')

No comments:

Post a Comment