RSS

Collation in SQL Server

16 Jul

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
 

One response to “Collation in SQL Server

  1. hermes outlet

    September 22, 2011 at 4:34 pm

    Thank you for a smart complaint. Me personally &amp our neighbour were definitely simply just getting ready to study during this. We ended up the pick up a guide from my community stockpile however i believe that I actually learned additional from that blog post. I will be rather delighted to determine these excellent info staying shared easily to choose from.

     

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

 
%d bloggers like this: