Changing collation

  • Hi,

    I need a bit of advice, I am currently going through a migration to a new data centre and SQL server 2008R2- 2012 upgrades. I am relatively new to this job (but not being a DBA) and historically they didn't have a DBA before I turned up so you can imagine what state the database servers were in.

    Currently our database servers are configured with the following collation SQL_Latin1_General_CP1_CI_AS and in our new environment I have elected to use Latin1_General_CI_AS, I know what is involved to change the collation (a bit of a ball ache).

    My question is:

    Am I signing myself for an unnecessary amount of work by changing to the correct collation, I know it's technically the right thing to do, but ultimately is it worth it given the massive task I have ahead of myself (100+ databases).

    What would you do?

  • You may very well be signing up for a lot of work (and troubleshooting afterward) that may not be necessary. What is reason for the change in the collation? Will it address some problem you are currently having?

  • I agree with Matt, will it help solve an issue or is this a want not a must for the bosses?

    MCSE SQL Server 2012\2014\2016

  • These two collations are effectively the same. They are both Case-insensitive, accent-sensitive, and have the same codepage, 1252.

    select collationproperty('SQL_Latin1_General_CP1_CI_AS','CodePage')

    select COLLATIONPROPERTY('Latin1_General_CI_AS','CodePage')

    One is windows collation and one is SQL collation but I dont think one is more correct then the other unless your applications require it.

    This KB article is specific to 2000/2005 but most probably still applies .

    How to post to get the best help[/url]

  • If you decide to go ahead with the change, my recommendation would be to build empty databases with the new collation and copy the data across with a tool such as SQL Data Compare from RedGate. I had to go through a similar process a few months back (we had a variety of collations so standardising on one was worth it). Get an evaluation copy, try it on a couple of your bigger databases to see if the timings are practical and then decide.


  • Thanks for the replies, there is no technical reason I want to do this, just for standardisation reasons really and me being a bit anal.

    Good to know that they resolve to the same code page, I think I might stick with SQL_latin1. Thanks for the Redgate advice I might consider that later down the line.

Viewing 6 posts - 1 through 5 (of 5 total)

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