SQL: Difference between Collations

Collations define the order that string data will be stored and how they will be compared on join/where conditions. If you try to compare two columns with different collations you may received the below error message. This may happen when using temporary tables or different databases in the comparison.

Msg 468, Level 16, State 9, Line 14
Cannot resolve the collation conflict between
"SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS"
in the equal to operation.

Collations that start with “SQL_” are the older ones and behave in a “SQL2000 way”, this means that it does not follow Windows rules for Unicode strings. Collations without the “SQL_” in the name are newer, compatible with windows, and follows the new rules of Unicode strings.

Because of this difference, the newer collations will be able to do an Index Seek on some specific conditions that SQL collations would cause an Index Scan. If your system uses string comparisons heavily, then this should be taken in consideration when creating the database.

See this great article on the differences and examples with deep analysis (Yeah it is really good!).


I’m out of time today, so go ahead and read the article. Research the web. Read more about this topic. Don’t take my word for granted, I’m researching and learning all the time, this blog is just to put together all the stuff I learn in a simpler and meaningful way. Do your homework too! =D



About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.
This entry was posted in SQLServer and tagged , , . Bookmark the permalink.

One Response to SQL: Difference between Collations

  1. mauriciorpp says:

    Adding a really good note from the folks @ sqlservercentral.com: How to make your database collation agnostic: http://www.sqlservercentral.com/articles/SQL+collation/134986

    The trick is to add “COLLATE DATABASE_DEFAULT” to the definition of any temporary table created by procedures from your database, as below sample:

    CREATE TABLE #myTempTable
    ( FooOne int,
    FooTwo varchar(50) COLLATE DATABASE_DEFAULT,
    FooThree nvarchar(50) COLLATE DATABASE_DEFAULT,
    FooFour bit )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s