SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Consolidation and Collation Settings


SQL Server Consolidation and Collation Settings

Author
Message
gs1975
gs1975
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 868
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
cnporteous
cnporteous
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 121
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.
gs1975
gs1975
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 868
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search