Collation of transformation database in SQL Server

This article defines collations and describes how to transform the collation of a database in Microsoft SQL Server. The same concepts and discussions about SQL Server 2000 apply to SQL Server 2005.

What are collation rules?

The collation specifies the bit pattern that represents each character. It also specifies the rules for sorting and comparing characters. Collations have the following characteristics:

Language

Case sensitive

Accent Sensitive

Distinguish Kana

To understand the collation that the server is currently using, you can run the Sp_helpsort system procedure in SQL Query Analyzer.

SQL Server 7.0 does not support databases that use multiple collations. Therefore, all databases created in SQL Server 7.0 use the default collation. SQL Server 2000 supports multiple collation rules. SQL Server 2000 databases can use a collation other than the default collation. In addition, SQL Server 2000 supports columns that use collations other than the collation created by the database.

Criteria for using the Use Collation option in DTS

SQL Server 2000 can contain multiple databases or columns, and they can use collations other than the default collation. Therefore, a new option named "Use Collations" is introduced in Data Transformation Services (DTS). The behavior of the Use Collation option is determined by the type of transformation to be performed. If you want to transfer data between two instances of SQL Server 2000 and the Use Collation option is enabled, the data is converted from the source code page to the target code page. If the Use Collation option is not enabled and the code pages in the two instances of SQL Server 2000 are the same, the data is transferred directly. If the code page is different, the data is converted from the source code page to the target code page. However, there may be an error in the conversion when transferring data.

Note: Collation is important if the collation is used for the data itself and the column uses a COLLATE clause. The Use collation option determines whether code pages are converted when data is transferred from one collation to another. The Use collation option does not affect whether the COLLATE property of the column definition is set. Therefore, if the source table contains a column with a specific collation created using the COLLATE clause, the collation will be preserved when the data is transferred, regardless of whether the Use Collation option is enabled in the Data Transformation Services Wizard.