Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

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.

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.


Filed under: Blog Tagged: sql server, syndicated, T-SQL

Comments

Posted by Jon Russell on 23 May 2011

Good to know, especially if you are dealing with a third party databases. Luckily, I have never come across this particular problem. You have inspired me to read up on the COLLATE clause. Opening BOL now.

BTW, SQL Authority is a great blog.

Posted by Pinal Dave on 25 May 2011

I am honored that you ended up on my blog. Since long time for all of us SQLServerCentral is the resource.

Thank you my friend.

Posted by sibir1us on 25 May 2011

Here is another article which will describe the collations concepts - what levels they can be on, what pitfalls there are and so on. sqlconcept.com/.../collation-concept-sql-server-database-column

Posted by sibir1us on 26 May 2011

And here is a good example why COLLATE database_default is NOT a good solution to the collation conflict problem: sqlconcept.com/.../beware-collation-database_default

Leave a Comment

Please register or log in to leave a comment.