Re: Different collations in a database
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 4 Sep 2007 09:15:49 +0000 (UTC)
(urquell@xxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- Different collations in a database
- From: urquell
- Different collations in a database
- Prev by Date: Format for six character date
- Next by Date: Re: Run all .sql files (scripts) in project (SQL Server 2005 SP2)
- Previous by thread: Different collations in a database
- Next by thread: Is ROLLUP really better than doing a nested query when rank is involved?
- Index(es):
Relevant Pages
|
