Resolve collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS"

Question

How can I fix this?

If I delete line 2, query work exactly.

SELECT case when ID_DonVi_Di > 0 then 'dv'+cast(ID_DonVi_Di as nvarchar) else MaChiNhanh_Di end as ID_NoiDi
    , case when ID_DonVi_Di > 0 then dvdi.Ten_DonViNgoai else cndi.TenChiNhanh end as NoiDi 
    , case when ID_DonVi_Den > 0 then 'dv'+cast(ID_DonVi_Den as nvarchar) else MaChiNhanh_Den end as ID_NoiDen
    --, case when ID_DonVi_Den > 0 then dvden.Ten_DonViNgoai else cnden.TenChiNhanh end as NoiDen
FROM 
    [dbo].[ToTrinh] a
left join
    dbo.DM_CHINHANH cndi on a.MaChiNhanh_Di = cndi.MaChiNhanh COLLATE Latin1_General_CI_AS
left join
    dbo.DM_CHINHANH cnden on a.MaChiNhanh_Den = cnden.MaChiNhanh COLLATE Latin1_General_CI_AS
left join
    [dbo].DM_DonViNgoai dvdi on a.ID_DonVi_Di = dvdi.ID_DonViNgoai
left join
    [dbo].DM_DonViNgoai dvden on a.ID_DonVi_Den = dvden.ID_DonViNgoai
where a.TonTai = 1

enter image description here

Answer 1

You need to change the collation of one field to match with the collation of the other field.

For example, if column A has collation Latin1_General_CI_AS...

and column B has collation SQL_Latin1_General_CP1_CI_AS...

You could do something like this:

CASE WHEN X > 0 THEN A ELSE B COLLATE Latin1_General_CI_AS END

Or something like like this:

CASE WHEN X > 0 THEN A COLLATE SQL_Latin1_General_CP1_CI_AS ELSE B END

Answer 2

The answer by @Jortx is 100% correct and solves the problem for this particular query. However, if you find that you need to do this over and over again, it might be worth changing the collation of one of the columns to match the other in the schema. The general syntax for that is:

alter table dbo.foo
    alter column bar nvarchar(400) collate SQL_Latin1_General_CP1_CI_AS;

That is, you'll be specifying all of the properties of the column the way you'd like them to be. Note that this is a size-of-data operation and so depending on the number of rows you have in your table it could take a while. Also, any non-clustered indexes on the column need to be dropped as well.

---

So how does something like this happen? Speculatively, one of your developers at some point created the table in another environment where the database had a different default collation. Then, in the process of getting that table ready to deploy to production scripted the table out. The script is explicit about what collation those columns have and so whatever the developer's collation was at the time of the scripting goes through up the environments. I'd recommend chasing that down. Not to give anyone a stern talking to, but to prevent this sort of thing going forward.

Are there valid reasons to have different collations within the same database? Sure! But in this case the two collations involved are so similar that it feels like one of them is an oversight.