Change the Collation of your Database
Using SQL Server 7.0
In SQL 7.0, there can be only one Collation per server, so to change the Collation of a
Database you need to change the Collation of the server itself. A Database with
a Collation that differs from the server cannot be attached to that server.
Solutions include setting up another SQL Server with the desired Collation and
transferring the Database using DTS or using BCP to export the data before
rebuilding the Server with the new Collation.See SQL Server Suggestion - Change the collation
Using SQL Server 2000 and Above
Unlike SQL 7.0, 2000 does support different collations for each database on the one server. The ALTER DATABASE COLLATE command will only change the *DEFAULT* collation of the database, it will not change collation of existing objects (e.g. columns & tables). They will still be stored in the old collation order even if the default is changed.
You'll need to
- Individually alter existing columns to the desired collation (you can generate a script based on sysobjects if you want to follow this path) - but this is more complicated and not recommended. See Altering Collation for columns on all tables in a database
- Recreate the tables with a script excluding the collation settings (as described below) and DTS the Data Into the new structure
Servers have a default Collation which is inherited by new Databases where a
Collation is not specified.
Databases have a Collation. Either unspecified (which uses the Server default)
or a specified Collation.
Columns have a Collation. Either unspecified (which uses the Database Collation)
or a specified Collation.
Warning: these steps assume that the Database uses a
single Collation for all objects specified at the Database level. If you require
different Collations for individual columns then do not proceed.
SQL Server 2000
- In Enterprise Manager: Generate a SQL Script that will recreate the Database.
Right-click on the Database > All Tasks > Generate SQL Script…
Show All > Tick All Objects
- Save the script to disk for later use.
- In Enterprise manager, make sure that the database is not in use by checking Management > Current Activity > Process Info. The
Database should not be listed under the Database column.
- In Query Analyzer run the following script to get a list of the files that the database uses
SELECT name, filename
- Copy the results into a text editor for later reference.
- In Query Analyzer run the following script to detach the database.
- In Windows Explorer: Rename the files that were listed in step 2 to <filename>OLD. E.g.
rename MyDatabase.mdf to MyDatabaseOLD.mdf
Note: Data files are usually in C:\Program Files\Microsoft SQL Server\MSSQL\Data\
- In Query Analyzer: Reattach the database with the name MyDatabaseOLD
@dbname = N'MyDatabaseOLD',
@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabaseOLD.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabaseOLD_log.ldf'
- Right-click on Databases and choose Refresh to show the renamed database.
- In Query Analyzer: Open the saved SQL script and search the SQL Script for any COLLATE xxx statements and
and replace with nothing (empty string).
This means that the database and columns will use the Collation specified as the server default collation.
- Optional: To use a particular Collation for the Database add a COLLATE statement to
the CREATE Database statement and specify the desired Collation. If a collation is not specified the server default collation will be used.
Removing all COLLATE statements means that the server default collation will be used.
- In Query Analyzer: Save and run the SQL Script to create a new Database with the desired
- In Query Analyzer
-- We are going to use DTS and DTS does not add data in the relationship order
-- Therefore we need to turn off the relationship data check temporarily
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
-- and turn off all triggers temporarily
EXEC sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
- In Enterprise Manager: Right-click on the Database with the incorrect Collation (the 'OLD' database) and choose All tasks >
Export Data… > Next
- Check the Source Database settings are correct (e.g. MyDatabaseOLDCollation)
then press Next
- Select the new empty Database as the Destination. E.g. MyDatabase
- Select 'Copy Objects and Data between SQL Servers' > Next
- (see image below)
Note: Don’t miss the tick on 'Use Collation'
- Next > Next > Finish
- In Query Analyzer
-- Finished with DTS
-- Therefore turn on the relationship data check again
EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
-- Therefore turn on the triggers again
EXEC sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
Which Collation to Use?
There is no 'recommended' Collation as different Collations will be used in different countries. As a guideline:
- United States installations should use the SQL_Latin1_General_Cp1_CI_AS Collation
- Non-United States installations in English speaking countries (eg. UK, Australia) should use the Latin1_General_CI_AS Collation.
Once a Collation has been decided on, it should be used for all servers in your organisation.
SQL Server Books Online
Topic: Data Conversion and Transformation Considerations
Section: Code Pages, Collation, and Non-Unicode Data Issues
Section: Copy SQL Server Objects Task
SQL Server Architecture - Changing Collations