Posted by Erland Sommarskog on 09/04/07 09:15

(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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]