Problems encountered in the project. The comparison results of the half-width character F00000 and the full-width character F00000 are the same, leading to duplicate primary key issues. The full-width ASCII code is 0xA3A8, while the half-width is 0x28. So why does SQL Server think it is the same?
In fact, the problem lies in the database sorting rules. Take a closer look at the documents of SQL Server. 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 that F00000 = F00000; 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 'f00000' = 'f00000'
Collate Chinese_PRC_CI_AS_WS
(The number of affected rows is 0)
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 %'