Got Collation conflict, How to avoid this?


The best way to avoid SQL Server’s collation conflict issue is that avoid have different collations in your database schema. 

  •  If your server and database have the same collation, you will never see the conflict collation issue.
  •  if all string columns in a database use the same collation, you will see less collation conflict unless you do cross-database query.  You don’t need explicit set the collation for string collations, they will pick up the database’s collation by default.
  • Keep in mind, changing existing database’s collation is allowed, but it wouldn’t changing existing table’s collation, so they will have change of mix of different collations.
  • I believe the common case of collation conflicts happens when your tempdb collation (which is your server collation) is different with your current database’ collation.  Objects created on tempdb will automaticlly pick the server collation, not the current database collation.  The best way to solve this is this article:http://sqlskills.com/BLOGS/KIMBERLY/post/Changing-Database-Collation-and-dealing-with-TempDB-Objects.aspx
  • When you hit collation conflict,  you can always use explicit collate clause to resolve the conflict.
  • If you have any other concern or suggestion,  please send me detail about your issue.  You can also vote for letting SQL Server solve this issue at http://connect.microsoft.com/SQLServer/feedback/Validation.aspx?FeedbackID=324910
Comments (1)

  1. nicolas says:

    i find it really annoying when i install two server with same OS, same locale and patching level and after installing MSSQL 2008 R2 i get two servers with different collations.

    it is a joke.

    one is

    Latin1_General_CI_AS (UK)

    and the other (guess?!)

    SQL_Latin1_General_CP1_CI_AS

    i know the UK is almost another US state but we are not officially there yet!

    best of luck solving this one.

Skip to main content