SQL Server Collation (collation) Conflicts and solutions

Source: Internet
Author: User
Tags character set sort tagname

What is a sort rule (collation)

About SQL Server collation, it is estimated that everyone is not unfamiliar, when creating a database we often have to choose a collation (conllation), generally we will notice that there are many kinds of rules for each language, such as standard Continental Simplified Chinese chinese_ There are dozens of of the collation rules for PRC.

What's the effect of these collations? Let's take a look at MS official explanations:

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

For example, in SQL Server 2005, collation names consist of two parts, such as Chinese_prc_ci_ai_ws

The first half refers to the character set supported by this collation, such as the collation of a CHINESE_PRC pointer to a Chinese simplified Unicode.

The latter part of the suffix means the following:

_bin                  Specifies the use of backward-compatible binary sort orders.
Specifies the binary sort order that uses the code-bit comparison semantics introduced in SQL Server 2005.
Sort by stroke
_ci (CS)
_ai (AS) is accent sensitive, AI does not differentiate, as distinguishes
_ki (KS) is the kana type distinguished, Ki does not differentiate, KS distinguishes
_wi (WS)

Now that the collation is so complex, the equal of the Union, Join, like, and so on can be done by default between columns that apply different collations, so there is a collation (collation) conflict.

Collation (collation) conflict.

As we know, SQL Server supports multiple collations starting with 2000. SQL Server 2000 databases can use a collation other than the default collation. In addition, SQL Server 2000 supports sorting rules specifically for columns.

This way, when we write T-SQL that spans tables, cross databases, and cross server operations, a collation violation occurs if the equal field collation is different.

For example, we first two table with the same structure, but the collation of the field is different:

Copy to Clipboard

--1. Create TableA.



TagName NVARCHAR (COLLATE) chinese_prc_bin


--2. Create TableB.



TagName NVARCHAR (COLLATE) chinese_prc_ci_as


When the table is built, execute:

Copy to Clipboard

--3. Try to join them

SELECT * from Tagstablea A INNER JOIN tagstableb B on a.tagname = B.tagname

A question like the following:


cannot resolve a collation violation between "Chinese_prc_bin" and "chinese_prc_ci_as" in the equal to operation.

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: info-contact@alibabacloud.com 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.