ALTER DATABASE ... ALTER COLLATION (FORCED).

As all of you know Microsoft has made a small problem for all SQL DBA and developers. If you have a database with any of SQL_xxxxx collations (for more details please have a look at the TERTIARY_WEIGHTS() function here), you will defenetily have a significant problem with index search and ordering performance if you'll try to upgrade your SQL Server with 2005 version. Some of developers was excited when first time reading this article, really :-).

Suddenly ALTER DATABASE .. ALTER COLLATION command will change database collation for you, but all objects will stay "as is" with old collation (by design). The only way to change collation for all existing objects is to create a new empty database with new desired collation and copy data into this new database (copy wizzard, for example).

To sort this issue out i created this small script (in attachment). It was tested both manually and with using VSTS DBPro, and i can confirm that you can change the collation of your database without any problem and manual work. As an input you should provide it with database with one collation, and as output you will have the same database with another (desired) collation. So as for me this is an analoque of ALTER DATABASE ... ALTER COLLATION FORCED command :-). How it works:

- you should use sqlcmd or SSMS in sqlcmd mode,

- specify database name as a parameter,

- specify desired collation as a parameter,

- run it and wait for results.

What is not covered by the script: table and index partitioning. I guess that if you are using these advanced options, you are smart enough to change this script to add required feature. Mostly i created this script for the following scenario: (1) detach database from SQL Server 6.5/7.0/200 version; (2) attach it to SQL Server 2005; (3) run this script; (4) use your database asap.

Some words about performance: than more tables with computed columns (computed column not at the end of the table) you have than more time you will have to wait. 100 Gigabyte db can be easily transformed within one hour on AI64 4way with EVA8000.

Also it is possible to use this script to study SQL 2005 system views and relationship between them.

Many thanks for any reply and comments.

 ---- January 2008

Many thanks to WaitForPete, script updated to fix these (and some other) issues.

change_collation.sql