Collation conflicts and Solutions

What is a sorting rule (collation) about SQL Server's sorting rules, it is estimated that everyone is no stranger, we often have to choose a sort rule (conllation) when creating a database ), generally, we will notice that there are many sorting rules for each language. For example, there are dozens of sorting rules for the standard Chinese Simplified Chinese Chinese_PRC. Sort

What is a sorting rule (collation) about SQL Server sorting rules, it is estimated that everyone is no stranger, when creating a database, we often choose a sort rule (conllation ), generally, we will notice that there are many sorting rules for each language. For example, there are dozens of sorting rules for the standard Chinese Simplified Chinese Chinese_PRC. Sort

What is collation)

It is estimated that the sorting rules of SQL Server are familiar to everyone. When creating a database, we often need to select a sorting rule (conllation ), generally, we will notice that there are many sorting rules for each language. For example, there are dozens of sorting rules for the standard Chinese Simplified Chinese Chinese_PRC.

What are the functions of these sorting rules? Let's take a look at MS's official explanation:

The sorting rule specifies the bit mode for each character. It also specifies rules for sorting and comparing characters. Sorting Rules have the following features:

Language

Case Sensitive

Distinguish Stress

Distinguish Kana

For example, in SQL Server 2005, the sorting rule name consists of two parts, such as Chinese_PRC_CI_AI_WS.

The first half is the character set supported by this sorting rule. For example, the Chinese_PRC pointer sorts UNICODE in simplified Chinese characters.

The meaning of the suffix is as follows:

_ BIN indicates the use of backward compatible binary sorting order.

_ BIN2 specifies the binary sorting order in which the bitwise comparison semantics introduced in SQL Server 2005 is used.

_ Stroke sort by strokes

_ CI (CS) is case sensitive, CI is case insensitive, and CS is case sensitive

_ Whether AI (AS) distinguishes stress, AI does not distinguish,

_ KI (KS) indicates whether Kana is distinguished. KI is not distinguished, and KS is distinguished.

_ Whether WI (WS) is fully half-width, WI is not differentiated, and WS is differentiated

Since sorting rules are so complex, equal operations such as Union, Join, and Like cannot be performed between columns with different sorting rules by default. Therefore, collation is enabled) conflict.

Collation conflict.

We know that SQL Server supports multiple sorting rules starting from 2000. SQL Server 2000 databases can use other sorting rules except the default sorting rules. In addition, SQL Server 2000 supports the creation of sorting rules for columns.

As a result, when we write a T-SQL for cross-table, cross-database, cross-server operations, if the equal field sorting rules are different, there will be a sorting rule conflict.

For example, we first see two tables with the same structure, but the sorting rules of fields are different:

Copy to Clipboard

-- 1. Create TableA.

Create table TagsTableA

(

TagName NVARCHAR (64) COLLATE Chinese_PRC_BIN

)

-- 2. Create TableB.

Create table TagsTableB

(

TagName NVARCHAR (64) COLLATE Chinese_PRC_CI_AS

)

Run the following command after the table is created:

Copy to Clipboard

-- 3. Try to join them

SELECT * from TagsTableA a inner join TagsTableB B on A. TagName = B. TagName

The following problems may occur:

The sorting rule conflict between "Chinese_PRC_BIN" and "Chinese_PRC_CI_AS" cannot be solved in the equal to operation.