Beware of Mixing Collations: Part 2- Converting Collations

With SQL
Server 2000 you are able to create databases or columns with a different
collating setting than the server (discussed in Part 1). This is
especially useful when you are trying to restore from a SQL Server 7.0 database
backup, where you are not sure of the collating sequence for the SQL Server 7.0
machine. If after your restore you desire to change the collation sequence,
then SQL Server 2000 will allow this. Which collation settings you want to
change will determine the method, and how much effort it will take to pull off
the collation change. This article will explain a couple of methods I have used
to successfully change collation settings.

There are
three distinct collation settings. The first one is the server collation setting
that is initially set when you install SQL Server. There is the database
collation setting, which defaults to the server collation setting, or can be
specifically defined to be different than the server collation setting when
creating a database. Lastly, there are collation settings for columns, that by
default take the database collation setting, or if you wish, you can specify a
collation setting when creating a column.

Changing
the collation setting for a server is quite complex, so I will save that for
the end. Although saying that doesn’t mean that changing the collation setting
for a database and all the character columns in the database is a piece of cake
either. Let’s first look at how you might change the collation setting for a
given column.

Changing a Columns Collation Setting

To alter
the collation setting for a given column you use the ALTER TABLE statement.
Columns that are referenced in a foreign key, check constraint, index, and/or
have distributed statistic cannot be altered without first dropping the column
constraints.

Let’s go
through a couple of examples. The first example will change the collation of a
varchar column from Danish_Norwegian_CS_AS to Latin1_General_CI_AS. This example uses the “ALTER
COLUMN” clause of the “ALTER TABLE” statement to make the column collation
conversion.

CREATE TABLE Test_tbl

  (Record_id   int PRIMARY KEY,

   Name     varchar(50) COLLATE Danish_Norwegian_CS_AS   NOT NULL

  )

GO

select * from information_schema.columns

go

—

— Here is where the collation setting is changed

—

ALTER TABLE Test_tbl ALTER COLUMN Name

            varchar(50) COLLATE Latin1_General_CI_AS NOT NULL

GO

select * from information_schema.columns

If you run the above code, then review the output
displayed from the information_schema.columns view, you can see that the
collation setting for the column NAME was changed from Danish_Norwegian_CS_AS
to Latin1_General_CI_AS.

Now let’s modify the above example slightly, by making the
NAME column in table Test_tbl the primary key. By doing this you now will not
be able to alter the NAME columns collation setting without first dropping the
primary key. As you can see in the example below, I have altered the table to
drop the primary key constraint, then altered the column collation, and finally
re-created the dropped primary key constraint.

CREATE TABLE Test_tbl

  (Record_id   int ,

   Name     varchar(50) COLLATE Danish_Norwegian_CS_AS   NOT NULL

constraint pk_name primary key

  )

GO

select * from information_schema.columns where table_name = ‘Test_tbl’

go

—

— Drop the primary key constraint

—

alter table Test_tbl Drop constraint pk_name

—

— Here is where the collation setting is changed

—

ALTER TABLE test_tbl ALTER COLUMN Name

	varchar(50)COLLATE Latin1_General_CI_AS NOT NULL

—

— Re-create the primary key constraint

—

alter table Test_tbl add constraint pk_name primary key (name)

GO

select * from information_schema.columns where table_name = ‘Test_tbl’

drop table test_TBL

Now imagine a database that contained a large number of
tables and a fair number of constraints (foreign key, check, and index). The
process of identifying all of the constraints, then building the script to
drop the constraints, altering each character column collation and the
re-creating the constraints sounds like a lot of work, right? Well it is! It can
be done, but it will be a time consuming, labor-intensive process. When using
this method you might be in for a few small bumps in the road, as you try to
ALTER COLUMNS that are still referenced by constraints.

I have
found this process to be too frustrating for most cases, except when I only
have a few columns that need their collation settings changed. If you have a
large number of character fields then you might want to read the section below,
on migrating a database and columns to a new database with new collation
settings.

Changing the Database Collation Setting

Each database has a default collation setting. For each
new character field created, that does not have a COLLATE clause, this default
setting is used. If you desire to change the default collation setting for a
database, you will need to use the ALTER DATABASE command. Here is an example
that sets the default collations of the SAMPLE_DB database to French_CI_AS
collation:

ALTER DATABASE SAMPLE_DB COLLATE French_CI_AS

Be aware that changing the default collation of a database does
not change the collation settings of any columns in existing
tables. The ALTER COLUMN clause of the alter table statement
will need to be used to alter the collation settings for any
existing columns in user-defined tables, as described above.