We currently install some of client's databases on SQL Server 2000 with the collation set to Latin1_General_BIN but we have one client that was installed and now running on SQL_Latin1_General_CP1_CI_AS and I was wondering if we can change the collation on the database to Latin1_General_BIN?
Would this have any adverse affects on the DB or the data within it? Our strings are nvarchar.
There should be no danger to the data itself. The basic steps are these;
1) change the database default collation (ALTER DATABASE)
2) Drop indexes on (n)varchar columns
3) Change collation on each individual (n)varchar column (ALTER TABLE)
4) Rebuild all the indexes dropped in step 2.
Thanks for the feedback. while waiting on your reply I tried the following steps from a Microsoft knowledge base which seemed to work.
1.) Created a new DB
2.) Created a script of all the tables and stored procedures leaving out all the triggers, constraints, primary foreign keys etc and using the Only script 7.0 compatible feature in DTS from the existing DB so not copying collations
3.) Ran the script on the new DB to create the tables and stored procedures
4.) Used DTS to transfer the data into the the new DB and only the data
5.) Created scripts for all the constraints, foreign keys, primary keys, and indexes from the source database leaving the options unchecked for the create and drop object.
6.) Ran the scripts on the new DB
I ran a schema comparison afterwards (SQL Delta) and it seemed fine. Would there be any affect on copying over the data using DTS?