As we all know that collation is a important part of Sql server storage engine. The collation defines who data is stored in Sql Server. The physical storage of character strings in Microsoft® SQL Server™ is controlled by collations. A collation specifies the bit patterns that represent each character and the rules by which characters are sorted and compared. It can have following characteristics

• Language
• Case sensitivity
• Accent sensitivity
• Kana sensitivity

We can use the sp_helpsort stored procedure to find out the collation of our server

Old versions like SQL Server 7.0 and SQL server 6.5 doesn’t support databases that have multiple collations. Therefore, all the databases that we create in SQL Server 7.0 use the default collation. SQL Server 2000 and later supports multiple collations. SQL Server 2000 databases can have collations other than the default collation. Additionally, SQL Server 2000 also supports columns that have collations other than the collations of the databases where they were created. in SQL server 7.0 if we need to change the collation of any database then the only way is to create database and all the objects on the destination server and then transfer the data by using DTS or the BCP utility.

Changing collation of database running SQL Server 7.0

To transfer a database from a computer that is running SQL Server 7.0 to a computer that is running SQL Server 7.0 and that has a different collation, follow these steps:

1. Back up the source database.
2. Use SQL Server Enterprise Manager to create scripts for all the objects in the source database.
3. To export the data from all the tables in the database, use DTS or the BCP utility.
4. Create a new database on the destination server by using SQL Server Enterprise Manager or the CREATE DATABASE statement.
5. Use SQL Query Analyzer to run the scripts that you created in step 2 to re-create all the objects in the destination database.
6. Import the data in the destination tables by using DTS or the bcp utility.

Changing collation of database running on SQL Server 7.0 to SQL server 2000

To transfer a database from a computer that is running SQL Server 7.0 to a computer that is running SQL Server 2000 and that has a different collation, follow these steps:

1. Back up the source database.
2. Use SQL Server Enterprise Manager to create scripts for all the objects in the source database.
3. If the columns must have a different collation than the default collation on the destination database, make the required collation changes to the appropriate columns in the scripts.
4. Create a new database on the destination server with the appropriate collation.
5. Use SQL Query Analyzer to run the scripts that you created in step 2 on the destination server to re-create all objects in the database.
6. Use DTS or the bcp utility to transfer the data.

Points to remember:

1. The new tables and columns have the same collation as the database unless you specify a different collation for the columns.
2. If you use DTS, Make sure that the Use Collation option is enabled when you transfer data from SQL Server 7.0 to SQL Server 2000.
3. If you use DTS, remember the objects are already created on the destination server with the appropriate collation, disable the Create Destination Objects First option.

Changing collation of database running on SQL Server 2000 to SQL server 2000 or later

To transfer a database from one collation in SQL Server 2000 to a different collation in SQL Server 2000, follow these steps:

1. Back up the source database.
2. Note if any columns use the COLLATE clause.
3. Create a new database on the destination server with the appropriate collation.
4. If no columns use the COLLATE clause, use DTS to transfer the data to the destination server. To do so, enable the Use Collation option for code page translation and to transfer the data to the new collation on the destination database. If any columns use the COLLATE clause, follow these steps:
5. Generate scripts for all the objects (not including the indexes, the triggers, the primary keys, the foreign keys, the default settings, and the constraints). Additionally, make sure that you enable the Only script 7.0 compatible features option to remove the COLLATE clause from the script.Note When you use the Only script 7.0 compatible features option, you can change the collation. However, any new SQL Server 2000 options (including user-defined functions, extended properties, the INSTEAD OF trigger, and indexes on views) will not be considered when the scripts are generated.
6. Run the scripts from step a on the destination database to create the objects with the destination database collation.
7. Use DTS to transfer only the data from the source database.
8. After the data is successfully transferred, generate scripts for all the constraints, foreign keys, primary keys, and indexes from the source database.Note On the Formatting tab of the Generate SQL Scripts dialog box, click to clear the following check boxes:

• Generate the CREATE <object> command for each object
• Generate the DROP <object> command for each object

1. Run the scripts from step d on the destination database.

Please note that the following methods do not change a database’s collation:

• Backup and restore: Restoring a database on a server that has a different collation than the server that is used for the backup does not convert the restored database to the new collation. The database collation remains as is.
• Detach and reattach: If you detach a database that was created with one collation and you reattach the database to another server that has a different collation, the collation of the database does not change. The collation of the database remains as is.
• Copy Database Wizard: The Copy Database Wizard essentially automates the process of detaching and reattaching. The collation of the database remains as is.