In this article, we will discuss some of the common issues that encounters with collation.
Collation issues arises with string type of datataypes like char, nchar, varchar, nvarchar, text etc...
Common scenerio of collation issues
- columns with different collations in where/on condition in a query
- columns with different collations in union/union all
First create two tables with different collation
create table tab1 (val varchar(10) collate Latin1_General_100_CI_AS) create table tab2 (val varchar(10) collate Latin1_General_100_CS_AS) insert into tab1 select 'ABC' union all select 'DEF' insert into tab2 select 'ABC'
Lets run a query with different collations with "on" condition
select * from tab1 t1 inner join tab2 t2 on t1.val = t2.valOUTPUT
Lets run a query with different collations with union
select val from tab1 union all select val from tab2OUTPUT
Cause: We are getting this error because collation of the columns used in the comparing condition are different.
Solution: In order to resolve the issue, we need to run the query with the same collation for both the columns. The simplest and easiest solution to resolve the same is to set the collation of both the columns as of database
select * from tab1 t1 inner join tab2 t2 on t1.val collate database_default = t2.val collate database_default select val collate database_default from tab1 union all select val collate database_default from tab2OUTPUT