Collation Conflicts in a SQL Server Join
I went to run this query recently:
select TOP 10 * from users a inner join Banned b on a.username = b.username
and got this lovely message.
I’d seen that message before, so I knew what was wrong. The collations for the two tables were inconsistent. Since this was a database that was upgraded from another version of SQL, and uses objects from a third party, I wasn’t surprised that a specific collation was used. I had created the “b” table myself, using database defaults, and they didn’t match the object.
I did a quick search since I couldn’t remember the exact syntax for the clause to add to my query. I ended up at a friend’s blog, Pinal Dave’s SQL Authority, and read this post: Cannot resolve collation conflict for equal to operation.
The fix is easy, add a COLLATE DATABASE_DEFAULT to the join condition to force a specific collation on the field. I could easily have added a COLLATE Latin1_General_CI_AS as well, but since I knew that the second field was database defaults, I did this:
select TOP 10 * from users a inner join Banned b on a.username COLLATE DATABASE_DEFAULT = b.username
Worked fine, and I was on my way.