SQL Server-Collation

Collation: Collation is set of rules which Determines how data are sorted and compared.

collations can be specified at many levels. When we install an instance of SQL Server ,we can specify the default collation for that instance. Each time we create a database,we can specify the default collation used for the database. If we do not specify a collation, the default collation for the database is the default collation for the instance.

  • We can get list of Collation by Querying.
select  * from  ::fn_helpcollations()
Let’s take example of two collation to know how it differ in behavior in context of Data insertion.
1.SQL_Latin1_General_CP1_CS_AS (Case Senstive,Accent-Sensitive).
2.SQL_Latin1_General_CP1_CI_AI (Case Insenstive,Accent-Insensitive).
first we create different table for both collation.
  • Employee_CS for (case sensitive collation i.e. SQL_Latin1_General_CP1_CS_AS )
  • Employee_CI for (case insensitive collation i.e. SQL_Latin1_General_CP1_CI_AI )
let’s Create table  Employee_CS,we will define collation at column level in this table which is case  and accent sensitive.
--Creating table Employee_CS ,define collation at column level which is case sensitive and accent sensitive
CREATE TABLE Employee_CS
    (
      id INT IDENTITY(1, 1) ,
      NAME NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS PRIMARY KEY
    )
 We have made Column Name Primary key in Table Employee_CS.Let’s insert same name but with different case.

 --insert to value for name which is not same  for case sensitive,both are different
INSERT INTO dbo.Employee_CS
        ( NAME )
VALUES  ( 'Bill'),('BILL')
--Insertion will be completed without error even we have primary key on Name column cause of collation we define on it.

We got Successful insertion of the data in the table cause Bill and BILL is different while we are defining case sensitive collation type on Column.

  • Let’s Create  Another table Employee_CI and define collation at column level which is case sensitive.
--Creating table Employee_CI ,define collation at column level which is case insensitive ans accent insensitive

CREATE TABLE Employee_CI
    (
      id INT IDENTITY(1, 1) ,
      NAME NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AI PRIMARY KEY
    )

Let’s insert Same name Bill and BILL into Name of Employee_CI.


--insert to value for name which is same for case Insensitive,both are different in term of case sensitive
INSERT INTO dbo.Employee_CI
 ( NAME )
VALUES ( 'Bill'),('BILL')
--Insertion steps throws error "Violation of PRIMARY KEY constraint"
  • As you run insertion step it throws error cause as collation is case insensitive.
  • Now Question is how we can compare column that having different collation?
  • Let’s insert Value in Table Employee_CI so we can compare Column Name with table Employee_CS
INSERT INTO dbo.Employee_CI
        ( NAME )
VALUES  ( 'Bill'),('BILLS')
  • When we write simple joining statement it throws error that collation conflict occur.
SELECT * FROM dbo.Employee_CI EI INNER JOIN dbo.Employee_CS ES ON EI.NAME = ES.NAME
--Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation.
  • We have to provide collation name where we are joining table based on column comparison as given below.
--You can give Default collation by stating DATABASE_DEFAULT.but you first got to know what collation current database have.
SELECT * FROM dbo.Employee_CI EI INNER JOIN dbo.Employee_CS ES
ON EI.NAME COLLATE SQL_Latin1_General_CP1_CI_AI= ES.NAME COLLATE SQL_Latin1_General_CP1_CI_AI
  • Here is code by which we can get collation at database level and server level.
SELECT DATABASEPROPERTYEX('sandbox','collation')--Gives Database level collation
SELECT SERVERPROPERTY('collation')--Gives Server Level collation
About these ads

One thought on “SQL Server-Collation

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