Changing database collation for existing databases

Changing database collation for existing databases

Post by JimLa » Thu, 12 Aug 2010 22:29:34


Hi,

We are upgrading from SQL2000 to SQL2008R2. We are taking the
opportunity to update the collation on our databases.

Sorry to bring this up again but there are a lot of partial answers
online and a lot of conflicting information. Our situation is that we
have a database with NO COLLATE clauses, i.e. all columns use the
database default (temp tables excepted).

The only Microsoft knowledge base article on this seems to be from 7.0
and 2000. I wonder if the same comments still apply.
http://www.yqcomputer.com/

So which is the best method (i.e. the simplest/fastest 100% correct
method) for updating the database collation and all data, given the
above details? The old collation is SQL_Latin1_General_CP1_CI_AS and
the new one will be Latin1_General_CI_AS.

As far as I can work out the options are:
1) A dynamic script that uses ALTER TABLE ALTER COLUMN statements on
an inplace database.
2) Create a new db with the correct collation - use SSIS to import
data and schemas.
3) Use SQL Compare.

Any advice very welcome.

Cheers,

James
 
 
 

Changing database collation for existing databases

Post by Erland Som » Fri, 13 Aug 2010 06:13:10

JimLad ( XXXX@XXXXX.COM ) writes:

Which is more difficult than it sounds, because indexes, foreign keys and
constraints must be dropped. I think did this once, but that was a migration
database with no FKs.


This is what have done. Although we have all our scripts on version control,
so we don't have to rely on scripting. And we did not use SSIS, but we
use BCP for the task. (We also already had the tools for this.)

Whether you actually can do this with SSIS I don't know. You don't want
it to copy the collation of the source database...

I would say that if you don't have any textual source for your database,
that is the first thing you should address.

--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Links for SQL Server Books Online:
SQL 2008: http://www.yqcomputer.com/
SQL 2005: http://www.yqcomputer.com/
SQL 2000: http://www.yqcomputer.com/

 
 
 

Changing database collation for existing databases

Post by JimLa » Wed, 18 Aug 2010 23:35:20


Thanks Erland.

SSIS Task 'Transfer SQL Server Objects Task' does NOT allow collation
change. The 'Use Collation' option just prevents data loss if the
source and target columns have different collations. If you copy the
schema as well as the data it will use the original collation.

So the standard option is to script everything out without collations,
run script on a new database with the new collation and then import
the data using bcp/SSIS etc. This leads to problems with old
unparseable or unexecutable stored procedures and old orphaned user
accounts, so some data cleanup may be required.

I have come across some script online that just changes the character
columns' collation using ALTER TABLE statement while dropping and
recreating all dependencies.
http://www.yqcomputer.com/

Assuming this works (from MVP blog, but will be testing!), I just
wanted to check whether there was any possiblility of data loss when
using the ALTER TABLE statement to change collation on a populated
column from sql_latin1_general_cp1_ci_as to latin1_general_ci_as?

Cheers,

James
 
 
 

Changing database collation for existing databases

Post by Erland Som » Thu, 19 Aug 2010 04:10:58

JimLad ( XXXX@XXXXX.COM ) writes:

Well, if you only have the source code in binary form, and disassemble
it when you need it, you may run into trouble. Keep your code under
version control, or at least on disk.


Depends. If you only use nvarchar, there is no problem. If you use
varchar, and your data includes character that is not in the varchar
repertoir in the new collation, that character will have to replaced
with a fallback. Now, since both collations in this case are based on
the same ANSI code page, this is not very likely, but I don't know all
characters on the top of my head.

You will find that ALTER TABLE also requires you to drop indexes,
constrinats etc, so there is a lot of issues there as well.

--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Links for SQL Server Books Online:
SQL 2008: http://www.yqcomputer.com/
SQL 2005: http://www.yqcomputer.com/
SQL 2000: http://www.yqcomputer.com/