August 31, 2012 at 2:20 am
Hi,
I have just been told to assist in a consolidation exercise where a number of our databases from different servers are moving to just 1 server (SQL Server 2008r2). The default server collation for the new server will be SQL_Latin1_General_CP1_CI_AS. A number of the databases have a collation setting of Latin1_General_CI_AS though. What is the best way to change the collation settings for these databases? A lot of the information I have already researched seems to be for SQL Server 2000 or contradicts each other.
Thanks,
George
August 31, 2012 at 2:40 am
Hey,
It depends on how you're migrating the databases. If you are attaching them or restoring from backup they will still hold their collations. If you need to change them you can do so from SSMS. Right click the DB, Properties then Options. But if these databases are connected to applications they will likely have this custom collation for a reason & you shouldn't change it.
The instance collation is there for new databases & your system databases.
If you do have databases with a different collation than the instance you can encounter problems when using the tempdb for example
IE. If you create a temp table, it will take on the system database collation. If you then compare some column in that temp table with a column in one of your databases you can encounter a "collation conflict" error.
There are ways around it by specifying collation when creating the temp table but its something to be aware of at least.
September 5, 2012 at 2:32 am
Hi,
Thanks for your assistance. It has been decided that we will set up two different instances instead using a different collation for each. I also had a plan put together using your advice and some scripts I had researched. It looks like this will come in useful for a future date.
Thanks again,
George
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply