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, for that let the data in our table be:-

Firstname	Lastname
Jill	Smith
Eve	Jackson
jill	William
Jill	Jackson
eve	Smith
john	Doe

1. Case Sensitive :-
   
   Let us consider the below query :-

   .
   SELECT * FROM T1 ORDER BY Firstname  COLLATE SQL_Latin1_General_CP1_CS_AS
   

   The result of this query is

   Firstname	Lastname
   eve	Smith
   Eve	Jackson
   jill	William
   Jill	Jackson
   Jill	Smith
   john	Doe

   
   
   In the above query the CS in "SQL_Latin1_General_CP1_CS_AS" stands for case sensitivity, however note that the case matters only if there is a conflict between two or more elements otherwise it sorts in normal alphabetical order. The real difference is noticed in case of comparison for example notice the result of below query :-
   

   .
   SELECT * FROM T1 where Firstname = 'jill'  COLLATE SQL_Latin1_General_CP1_CS_AS
   

   
   

   Firstname	Lastname
   jill	William

   
   You can notice that the above comparison is case sensitive, since originally we had three records whose Firstname is jill (or Jill) but as we ran the query with case sensitive collate therefore only one record with Firstname 'jill' is selected.
   
   To get the sorting in actual case sensitive manner you need to use binary collate, for example consider the query :-
   

   .
   SELECT * FROM T1 order by Firstname  COLLATE Latin1_General_bin
   

   
   The result of above query is

   Firstname	Lastname
   Eve	Jackson
   Jill	Jackson
   Jill	Smith
   eve	Smith
   jill	William
   john	Doe

   
   In binary collate upper case letters and lower case letters are treated as separate characters therefore all the upper case letters comes before lower case letters when sorted in ascending order.


2. Case Insensitive :-
   
   Consider the query :-
   

   .
   SELECT * FROM T1 ORDER BY Firstname COLLATE SQL_Latin1_General_CP1_CI_AS
   

   The result of above query is

   Firstname	Lastname
   Eve	Jackson
   eve	Smith
   jill	William
   Jill	Jackson
   Jill	Smith
   john	Doe

   
   
   As you can see the sorting is irrespective of case. Also to notice case insensitive comparison checkout the result of below query:-
   

   .
   SELECT * FROM T1 where Firstname = 'jill'  COLLATE SQL_Latin1_General_CP1_CI_AS
    

   
   

   Firstname	Lastname
   Jill	Smith
   jill	William
   Jill	Jackson

   
   In the above query the collation is case insensitive that is why "jill" and "Jill" are treated as same and three records are selected instead of just one.

• 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'
• Get collation of SQL Server : To get the collation that is set on SQL server use the query :-
  

  .
  select serverproperty('collation')



• To get collation of a specific database on SQL server use the query :-
  

  .
  select databasepropertyex('MY_DB','collation')