Beware: collation database_default

I have seen in so many blog posts a proposed solution to the ‘cannot resolve the collation conflict’ problem; many of the blog posts and forums suggest that the collate database_default will solve the problem.

As I got a similar problem a few days ago, I stopped for a second and I though ‘hm, can this be the optimal solution and what problems can it cause?’

If you need to read about the collation concept, its levels, settings and restrictions, read it here.

As a next step I create the following two tables, with 2 columns each: one nvarchar and one int. One of the nvarchar columns picks up the collation setting from the database collation, and the other one has a finnishswedish cs as collation explicitly specified by me.

First things first – if I do not specify the collation setting for the columns of the join predicates, then the query fails with the following error:

Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between "Finnish_Swedish_CS_AS" and "Latin1_General_CI_AI" in the equal to operation.

Let’s look at the results now. To keep this post compact, I will just post a screenshot of the results of the 4 queries above:

Hm, so the COLLATION does really affect the results...

So, what is the bottom line? The reality is that we get different resultsets, depending on the collation settings expressed in our query. We are looking for Göran Lindén – the guy that has that funny [´] sígn in his name, and only for him. (We do not want any other boring records without [´] sígns in our resultset! )

It turns out that the collate database_default is not an ultimate way to solve the problem of the collation resolution when joining tables. It really depends on what records we are looking for, and what character encoding we want to acknowledge or ignore.

After all, think about it this way: if there is a column with a different collation in your database, the chances are that maybe there is a good reason for it.

Powered by Good Will. (Will is the person who offers his sympathy to others together with ideas, but who does not give any guarantee about the completeness of the concepts or the immaculate perfection of the scripts on this site. Use this site to grasp SQL Server concepts and to get knowledge. )