Nate Johnson explains why you want collation consistency when joining tables together on varchar/nvarchar columns:
There’s a subtle difference here, vs. those many community blog posts, which I’ll repeat. The columns are of the same type. Just different collations.
And when the collation on the join predicates is different, bad things happen. Let’s take
CustomerNumberfor example. On the ERP side, it’s a
Latin1_General_100_CI_AS. On the internal & web apps side, it’s a
SQL_Latin1_General_CP1_CI_AS. As you might imagine, this is a prime field for joining because it’s the main customer identified throughout all the systems.
Let’s be clear here. This is a numeric value only. Did it need to support Unicode? Absolutely not. Should it have been an
bigint? Probably. But did The ERP designers choose to make it Unicode string anyway? Yep.
Read on to see how Nate tries to dig himself out of this hole.