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:
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:
|| 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
|| is accent sensitive, AI does not differentiate, as distinguishes
|| is the kana type distinguished, Ki does not differentiate, KS distinguishes
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:
cannot resolve a collation violation between "Chinese_prc_bin" and "chinese_prc_ci_as" in the equal to operation.