Collation question

  • Hello everbody,

    I'd like to get some advice about whether or not changing collations. One of my customers with about 40 servers (SQL2K) has a few servers with the default collation Latin1_General_CI_AS while the standard for the servers should be SQL_Latin1_General_CP1_CI_AS. They now asked me to change the collation on the servers having Latin1_General_CI_AS by rebuilding the master database. But during testing rebuildm.exe I noticed that the "SQL_" collations are only there for compatibility with older versions and Latin1_General_CI_AS is basically the new version of SQL_Latin1_General_CP1_CI_AS and I wouldn't make much sense to change it anyway. I compared the character mapping and they are exactly the same. So are these two collation the same?

    Can anyone confirm this or tell me what is different other than the name.

    Thanks a lot

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • Although I can't say exactly what the difference is, I have had problems related to these two collations conflicting.

    As I recall, I restored a database with the old collation onto a server that defaults to the new. A couple of stored procs in the database would create a temp table, populate it, then join it to a table in the database on a varchar column. The collation in tempdb was the new, the collation in the db was the old, and SQL choked on the comparison.

    Rather than rebuilding my master, I just tweaked the couple of procs that were failing to set the collation for the varchar columns to match my database, and everything has worked fine ever since (as far as I can tell).

  • Thanks or the reply Skeane.

    I know that there are issues than comparing data from different collations, even though they look very much the same. But in my case the whole server has the same collation so this shouldn't be an issue. And if they need replication or linked servers they can configure if they want to use the local or the remote collation.

    It's just some administrators who want to have everything according to a certain standard even if the standard might be wrong.

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • We had issues when developers were creating temporary tables and TEMPDB and that database own a different collation than source database, which causes a lot of errors.

    Finally, they decided use variables in order to maintain temporary tables

     

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply