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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

1 Response to Collation Conflicts in a SQL Server Join

  1. Adam Mikolaj says:

    Ugh…been there as well.

    Since I have been doing clean up at my current job I have ran into that from time to time. Seems that there was no standard collation ever set. I found it’s always one of these 2:
    • SQL_Latin1_General_CP1_CI_AS
    • Latin1_General_CI_AS

    Hard part is to choose a standard and roll it out to all servers.


Comments are closed.