This was asked on twitter recently and I gave the answer there. I decided to write a blog post about this because I can use over 140 charaters here instead.

You will see the Cannot resolve collation conflict for equal to operation error when you try to join 2 tables. let’s take a look at what we need to do to resolve this. First create and populate these two tables

use tempdb
go



create table Test (SomeColumn varchar(100) not null)
insert Test values('bla')
insert Test values('test')


create table Test2 (SomeColumn varchar(100) collate Traditional_Spanish_CI_AI not null)
insert Test2 values('Niño')
insert Test2 values('bla')

Now run the following join between the Test and Test2 tables

select * from Test t1
join Test2 t2 on t1.SomeColumn = t2.SomeColumn

Here is the error you will get

**Server: Msg 446, Level 16, State 9, Line 1

Cannot resolve collation conflict for equal to operation.**

To quickly fix this you can use collate in your SQL, you have to make sure that the collation is the same on both columns. So you either add the collate with the collation of the column in test2 to the column in the test table or vice-versa. Here is one example

select * from Test t1
join Test2 t2 on t1.SomeColumn = t2.SomeColumn collate Traditional_Spanish_CI_AI

You can also apply collate on the other column…first we need to know what the default was in your database. We can use the ANSI information_schema.columns view to get this info, we need to use the collation_name column. Run the following query to grab it

select column_name,collation_name
from information_schema.columns
where table_name = 'test'

In my case it is SQL_Latin1_General_CP1_CI_AS, now the query becomes the following

select * from Test t1
join Test2 t2 on t1.SomeColumn collate SQL_Latin1_General_CP1_CI_AS = t2.SomeColumn

Just for fun let’s see what the collation is for the column in the Test2 table

select column_name,collation_name
from information_schema.columns
where table_name = 'Test2'

As expected it is Traditional_Spanish_CI_AI

You can use the ::fn_helpcollations()function which returns a list of all the collations supported by SQL Server to get more info about the collation

Run the following query (and yes :: is not a typo)

select * from ::fn_helpcollations()
where name ='Traditional_Spanish_CI_AI'

The query returns the following description for Traditional_Spanish_CI_AI’

Traditional-Spanish, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive

One thing to be aware of is that when using collate it needs to do a conversion so you might get performance problems…make sure to check those execution plans.

Hopefully this will help some person when dealing with this in the future.

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum