Changing the Server Collation Setting
Changing the server collation setting is not an easy
task. There is no ALTER SERVER command to help change the collation setting
for the server. In order to change the server collation you will need to
rebuild the server.
you will need to export all the data in the existing databases. Gather up
and/or generate all the scripts to recreate the user databases and objects in
those user databases. Drop all the user databases and rebuild the MASTER
database. When you rebuild the MASTER database, you will specify your new
if you are generating table create scripts from a SQL Server 2000 database then
they might contain COLLATE clauses. Prior to executing generated scripts scan
your CREATE TABLE and DATABASE scripts to verify that there are no COLLATE
clauses. If you do not do this, then potentially your newly migrated columns
might end up with their original collation settings.
have rebuilt the MASTER database, then you can re-create all the user
databases. Each object in each user database will need to be re-created with
the collation setting of the database. If your create table statements do not
have any COLLATE clauses, then all columns will be given the default collation
setting for the database. Once all of your tables are created you will need to
import the data using the files exported from the database that contained the
wrong collation settings.
not a simple task, but it can be done. This is why choosing your collation
setting up front is very important.
Alternative Method for Migrating Database and All Columns
Within to a New Collation
If you need to change all of the collation settings in a
database, you might consider migrating your data to a new database that has the
desired collation setting. This typically is how I migrate from one collating
sequence to another.
accomplish this by performing the following multi-step process. This process can
be done in a number of different ways; I will walk you though the steps I
Step 1: Create a new database with the
desired default collation setting.
Step 2: Generate all of the CREATE TABLE
statements from the database that has the wrong collation settings. Only
generate the create table statements; do not generate any constraints. If your
scripts where generated from a SQL Server 2000 database, remember to review
your scripts for COLLATE clauses (see note above).
Step 3: DTS the data from the database
that has the wrong collation settings, to your newly created database.
Step 4: Generate all of the constraints
from the database with the wrong collation settings. Apply the generated
constraints to your newly created and populated database. If you do not have
clean data, you may have to apply some of the constraints with the NOCHECK
Step 5: Generate the scripts to create all
views, stored procedures, roles, user, and/or any other objects you might need
from the database with the wrong collation settings and execute the generated
script on the new database.
Step 6: Backup your newly created database
and restore it over the top of the database with the wrong collation. Of
course, only do this if you want to replace your original database with your
new database, which has a new collation setting.
is not simple, and is still error prone. You need to take care when generating
scripts that nothing is missed and the scripts generated build things in the
that SQL Server tends to generate things in alphabetical order, which is not
always an appropriate order. I have resorted to using a third party tool "SQL
COMPARE" from Red Gate Software for generating my scripts. I've also used this
tool to compare both old and new databases to make sure that no objects were
missed when migrating objects from the database with the wrong collation
settings to the new database.
this method is time consuming, I've found that it is a reliable method to
convert from one collation to another. I'm sure there might be other methods
out there for converting collation settings.
Converting from one collation setting to another is not
easy. There is no simple tool to perform this transformation. In order to
minimize collation issues it is best to require all server databases to have
the same collation settings, even if it requires you to migrate your data from
one collation setting to another.
See All Articles by Columnist Gregory A. Larsen