Beware of Mixing Collation with SQL Server 2000 - Part 1: "et's look at an example that will demonstrate what I am talking about. The example below tries to join data from two different tables on columns that have different collating sequences.

create table #a (char_set1 varchar(50) collate Latin1_General_CI_AS)
create table #b(char_set2 varchar(50)collate Latin1_General_BIN)
insert into #a values ('collate')
insert into #b values ('collate')
select * from #a join #b on char_set1 = char_set2

When the code above is executed, the select statement returns the following error:

Cannot resolve collation conflict for equal to operation.

Why does the SELECT statement get this error? This error happens because char_set1 column in table #a has collation of Latin1_General_CI_AS, where as char_set2 column in table #b uses a collation of Latin1_General_BIN.

Of course, SQL Server allows you to write code so you can join the above two tables, even though the join columns each have a different collation setting. By Changing the SELECT statement so that one of the columns does an explicit collation conversion prior to joining, resolves the issue shown above. Below char_set2, which has a collation setting of Latin1_General_BIN is explicitly converted to Latin1_General_CI_AS using the COLLATE clause:

select * from #a join #b on char_set1 = char_set2 collate Latin1_general_CI_AS

By using this explicit conversion from one collation to another you can then join both tables. Now if you really have to store and compare columns with different collation, then SQL Server is great at providing you this functionality. Although if you have no business requirements to store data with different collations then by all means don't. Storing data of varying collations causes additional unnecessary programming considerations therefore choose your collation requirements wisely."