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

(urquell (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx