Collation….

This is a quick post to talk about collation – and why [NOT] to change it from the default.

Microsoft defines collation as:

“Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales. For example, in an ORDER BY clause, an English speaker would expect the character string ‘Chiapas’ to come before ‘Colima’ in ascending order. However, a Spanish speaker in Mexico might expect words beginning with ‘Ch’ to appear at the end of a list of words starting with ‘C’. Collations dictate these kinds of sorting and comparison rules. The Latin_1 General collation will sort ‘Chiapas’ before ‘Colima’ in an ORDER BY ASC clause, whereas the Traditional_Spanish collation will sort ‘Chiapas’ after ‘Colima’.”

If you have 2 tables with different collation and try to do a join, you’re gonna get:
an error like: Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AI” in the equal to operation.

Why jack with collation in the first place? 99% of the time you should just leave it alone, unless you have a really good reason….

Here’s the workaround when someone throws this at you.

INNER JOIN #Translations b
ON a.Sourceid COLLATE SQL_Latin1_General_CP1_CI_AS = b.SourceID COLLATE SQL_Latin1_General_CP1_CI_AS;

This forces the join to use the same collation.