/ * ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------
/ * Welcome to reprint, please keep this statement information
/ * Author: Weng Yan enhydraboy@yahoo.com.cn
/ * ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------
This is a problem that I am experiencing in the actual project. The results of 03BHL01001 (Shanghai) and 03BHL01001 (Shanghai) comparison are the same. Leading the cause of repeated primary key issues.
03bhl01001 (Shanghai) and 03BHL01001 (Shanghai) are different, that is, the former's brackets are all corner of parentheses characters, the latter is a half-angle parentheses character. The ASCII code of the full corner of parentheses characters and half parentheses is obviously different. Full angle (ASCII code is 0xA3A8, while half-angle (0x28. So why SQL Server thinks is the same?
The problem is actually on the rules of the database, let us study the documentation of SQL Server carefully. SQL Server's sort rules consisting in such a few, code page, case sensitive, distinguished, distinguished, and width. The last one is not further mentioned in the online help of SQL Server. In fact, the problem encountered is due to this reason. Distinguishing the width: Specify the single-byte representation (half-angle) and double-byte representation of the SQL Server distinguish between the SQL Server. If there is no choice, SQL Server will consider the single-byte representation and double-byte representation of the same characters.
By default, when installing SQL Server Chinese version, SQL Server helps you choose the sort rules for Chinese_PRC_CI_AS (Chinese-PRC, Case-Innsitive, Accent-Sensitive, Kanatype-Innsitive, Width-Insensitive), is Chinese, not case sensitive , Distinguish between tones, do not distinguish between false names, and do not distinguish width. Therefore, naturally agree with 03BHL01001 (Shanghai) = 03BHL01001 (Shanghai).
Therefore, the correct choice should be that the suffix is the Chinese sort rule of WS. In this example we should choose Chinese_PRC_CI_AS_WS.
Let's take a look, the specified sort rules are chinese_prc_ci_as_ws, how is it?
SELECT 1 where '03bhl01001 (Shanghai)' = '03bhl01001 (Shanghai)' Collate Chinese_PRC_CI_AS_WS
-----------
(The number of rows affected is 0)
It seems that this problem is solved.
important hint:
How to check the use of that sorting rules? You can use the following SQL statement.
SELECT *
From :: fn_helpcollations ()
You can query information about all sort rules.
Isolated information of all Chinese sort rules
Select * from (SELECT * from :: Fn_HelpCollations ()) a where name like 'chinese%'
