SQL Server Collation (collation) Conflicts and solutions

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:

Language

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.

CREATE TABLE Tagstablea

(

TagName NVARCHAR (COLLATE) chinese_prc_bin

)

--2. Create TableB.

CREATE TABLE Tagstableb

(

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:

The

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