Skip to content

Collation Conflicts in a SQL Server Join

May 23, 2011

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.

collation

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 these ads

From → Blog

One Comment
  1. 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.

Follow

Get every new post delivered to your Inbox.

Join 4,773 other followers

%d bloggers like this: