RSS

Category Archives: Collation in SQL Server

Collation in SQL Server

What is collation

In one line, collation is set of rules that governs how to use characters, alphabets or language in SQL Server.

We came across problem of “Cannot resolve collation conflict” whenever we try to fetch data from two databases having different collation.Here i am elaborating the issue with detail by taking example.

Example

Let’s start from the scratch……..

Step 1 : we are going to create two database with different collation.

create database DB1 collate Latin1_General_CI_AS

Create database DB2 collate Latin1_General_CI_AI

Step 2 Creating tables and insertion of data.

use DB1

Create table Test1(id1 int not null primary key, Name1 varchar(255))
Insert into Test1 (id1,Name1) values (1,’Amit’)

use DB2

Create table Test1(id int not null primary key, Name varchar(255))
Insert into Test1 (id,Name) values (1,’Amit’)

Step 3 : Let’s fire two query as below :

Query 1 : Joining based on ID (Int datatype)

select * from DB1.dbo.Test1 T1 join DB2.dbo.Test1 T2 on T1.id1=T2.id

we will get the result as below :

Id1    Name1   Id   Name

1       Amit       1     Amit

** that is what we want……

Query 2 : Joining based on Name (varchar datatype) :collation will play role here

select * from DB1.dbo.Test1 T1 join DB2.dbo.Test1 T2 on T1.Name1=T2.Name

we will get error in this case as below..

Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “Latin1_General_CI_AS” in the equal to operation.

Question

Why we are getting error in second case, though we are able to execute the first query.

Answer

This happens because of different collation of database, difference in database collation won’t effect  int datatype, hence generating result in first case while throws error when we try to fetch data based on joining of Name (varchar datatype) column

Solution

Just a minor updation in query will do our work, i.e set a collation to the query no 2 either of database DB1 or DB2, have a look on collate part in below query

we can do either way

1. Select * from DB1.dbo.Test1 T1 join DB2.dbo.Test1 T2 on T1.Name1=T2.Name
    collate Latin1_General_CI_AS

2. Select * from DB1.dbo.Test1 T1 join DB2.dbo.Test1 T2 on T1.Name1=T2.Name
    collate Latin1_General_CI_AI

Both will yield result as desire, in our case we will get result as follow

Id1    Name1   Id   Name

1      Amit       1    Amit

Author : We will discuss on some other topic in next post.

Advertisements