Different collations in a database

Because of poor upgrading routines I have a SQL Server 2000 database
that has several collations on column level.
The SQL Server itself is correctly setup to use Finnish_Swedish_CI_AS,
the same as the database. However, a number of columns in various
tables also have Danish_Norwegian_CI_AS and
SQL_Latin1_General_CP1_CI_AS.
Obviously this is causing problems and I would like to change all of
the fields back to database default.

I've ran a script to do just that on a copy of the database and
everything seems to be in order afterwards. I.e. the sort order is
correct and I can use the fields in joins without getting collation
conflicts.

But I'm worried that there is something more I need to test, since
I've been reading many posts about the issue and most seem to
recommend that you unload the data and load it again into a clean
database with the correct collation.
Is that really necessary when changing between collations that use the
same codepage?

What else do I need to test to verify that the change has been
successful or prove that it is incomplete?

Best Regards,
Bogi Palsson

Re: Different collations in a database

(urquell@gmail.com) writes:
> Because of poor upgrading routines I have a SQL Server 2000 database
> that has several collations on column level.
> The SQL Server itself is correctly setup to use Finnish_Swedish_CI_AS,
> the same as the database. However, a number of columns in various
> tables also have Danish_Norwegian_CI_AS and
> SQL_Latin1_General_CP1_CI_AS.
> Obviously this is causing problems and I would like to change all of
> the fields back to database default.
>
> I've ran a script to do just that on a copy of the database and
> everything seems to be in order afterwards. I.e. the sort order is
> correct and I can use the fields in joins without getting collation
> conflicts.
>
> But I'm worried that there is something more I need to test, since
> I've been reading many posts about the issue and most seem to
> recommend that you unload the data and load it again into a clean
> database with the correct collation.
> Is that really necessary when changing between collations that use the
> same codepage?

The main reason is that reloading of data into a database that is built
from scripts is recommended, is that to change the collation of a column,
you need to drop all indexes and all referencing foreign keys for that
column and then reapply them. Since this easily could go wrong unless you
have good tools, a new database and reloading is a safer way.

But if all your character columns are unindexed there are no such issues,
and ALTER TABLE ALTER COLUMN will work well.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx