/*--------------------------------------------------
/* Author: Weng Yan enhydraboy@yahoo.com.cn
/*--------------------------------------------------
This is a problem in my actual project. The comparison results of 03BHL01001 (Shanghai) and 03BHL01001 (Shanghai) are the same. Duplicate primary key issues.
The difference between 03BHL01001 (Shanghai) and 03BHL01001 (Shanghai) lies in that the brackets of the former are full-angle brackets, while those of the latter are half-angle brackets. The ascii code of the full-width and half-width parentheses is obviously different. Full-width (the ASCII code is 0xA3A8, and the half-width (0x28. So why does SQL Server think it is the same?
The problem lies in the database sorting rules. Let's take a closer look at the SQL Server documents. The sorting rules of SQL Server are composed of the following parts: code page, case-sensitive, stress-sensitive, and width-sensitive. The last one is not mentioned in the SQL Server online help. In fact, the problem encountered in this article is caused by this reason. Width differentiation: specify that SQL Server differentiates single-byte notation (halfwidth) and double-byte notation (fullwidth) of the same character ). If not selected, SQL Server considers the single-byte representation of the same character as the dual-byte representation.
By default, when you install the Chinese version of SQL Server, the sorting rules Selected by SQL Server are Chinese_PRC_CI_AS (Chinese-PRC, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive ), it is a Chinese character, not case sensitive, accent sensitive, not Kana, not width sensitive. Therefore, we naturally agree with 03BHL01001 (Shanghai) = 03BHL01001 (Shanghai ).
Therefore, the correct choice should be the Chinese sorting rule suffixed with WS. In this example, we should select Chinese_PRC_CI_AS_WS.
Let's take a look. What happens after the specified sorting rule is Chinese_PRC_CI_AS_WS?
Select 1 where '03bhl01001 (Shanghai) '= '03bhl01001 (Shanghai )'
Collate Chinese_PRC_CI_AS_WS
(The number of affected rows is 0)
It seems that this problem has been solved.
Important:
How can I check which sorting rule is used? You can use the following SQL statement.
SELECT *
FROM: fn_helpcollations ()
Query the information of all sorting rules.
Find information about all Chinese sorting rules
SELECT * FROM
(
SELECT *
FROM: fn_helpcollations ()
WHERE name like 'Chinese %'
