Collation of transformation database in SQL Server

Source: Internet
Author: User
Tags microsoft 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:


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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.