SQL Server Performance Forum – Threads Archive
Collation differences….
Just started a new role as SQL Server DBA with a financial institution. Bloke who was doing the job before has created all the instances of SS2K (SP4) with the default collation of Latin1_General. I am about to start my first go at installing a new instance of SS2005 on a new box and would like to revert to the tried-and-tested collation of /1252, Dictionary Order, case-insensitive/. Anyone think this is a bad idea? Also – I’ve never tried to change the collation order in an existing instance.
Is it possible?
Anyone tried it?
How did it go? Thanks to all for any advice given.
A lack of planning on your part does not constitute and emergency on mine.
Yes, collation change is possible…
I believe Some people in this forum tried… You may want to check out this link to change the collation.
http://www.databasejournal.com/features/mssql/article.php/3302341 http://msdn2.microsoft.com/en-us/library/ms144259.aspx#
To rebuild system databases and specify a new system collation Insert the SQL Server 2005 installation media into the disk drive. Run the following command from the command prompt: Copy Code
start /wait <CD or DVD Drive>setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> SQLCOLLATION=<NewSystemCollation>
For INSTANCENAME, use MSSQLSERVER for a default instance. For a named instance, specify the instance name. Important:
The resource database (mssqlsystemresource) is restored from SQL Server 2005 distribution media. When you rebuild the resource database from distribution media, all service packs and hotfix updates are lost, and therefore must be reapplied. Before you proceed, see the [REBUILDDATABASE] section for more information.
MohammedU.
Moderator
SQL-Server-Performance.com
Yes you can perform the collation change, but before that I would like to know that if the application can connect to the user databases and continue its functionality without any issues then no need to change the server collation. I believe this has to be tested thoroughly from the application side about having multi-collation settings on server and databases side, if this is a vendor based application then make sure to involve them as well. Otherwise talk to the Developers to understand what they want to achieve and without changing the server collation you can sit with database-table-column based collation settings Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
In SQL 2005 u can change collation at DB level but this is not changed at table level. U need to use some trick for this as follows.
A helpful query to change the collation. This is dynamica query and handy one.
select ‘ALTER TABLE ‘+object_name(col.id)+’ alter column ‘ + col.name + ‘ char(‘+rtrim(cast(col.length as char(10)))+’) collate SQL_Latin1_General_CP1_CI_AS’ from syscolumns col, sysobjects obj where col.id = obj.id and obj.xtype = ‘u’ and col.collationid <> 0
Regards,
Ashish Ashish Johri
]]>