Collation Issues SQL Server

In previous article, we learnt about collation in SQL Server

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

Lets take examples to understand the issues described above

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.val

OUTPUT

Lets run a query with different collations with union

select val from tab1
union all
select val from tab2

OUTPUT

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 tab2

OUTPUT