创建带主键的表类型后插入不同特殊字符字符串触发主键冲突

问题根源 #

问题出在你使用的SQL_Latin1_General_CP1_CI_AS排序规则上——它是基于单字节Latin1编码的排序规则，不支持多字节的Unicode补充字符（比如Emoji）。当这类超出Latin1编码范围的字符进入数据库时，会被映射到同一个无法识别的替代值，导致数据库判定两个不同的Emoji为相同的主键值，最终触发约束冲突。

你可以通过以下查询验证这一点：

SELECT 
    N'📺' AS Emoji1, 
    N'💥' AS Emoji2,
    CASE WHEN N'📺' = N'💥' COLLATE SQL_Latin1_General_CP1_CI_AS THEN '视为相同' ELSE '视为不同' END AS 排序规则下的比较结果

执行后会返回视为相同，直接证明当前排序规则下这两个Emoji被判定为相等。

解决方法 #

有两种可靠的修复方式：

1. 使用支持补充字符的排序规则 #

将表类型列的排序规则改为支持完整Unicode补充字符的类型，比如Latin1_General_100_CI_AS_SC（SC代表Supplementary Characters，专门支持Emoji这类扩展字符）：

CREATE TYPE [dbo].[String_Table] AS TABLE(
    [Value] [nvarchar](255) NOT NULL COLLATE Latin1_General_100_CI_AS_SC,
    PRIMARY KEY CLUSTERED 
(
    [Value] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)

2. 采用二进制比较规则 #

如果不需要按语义排序，只需要精确匹配字符二进制值，可以改用二进制排序规则，比如SQL_Latin1_General_CP1_CS_AS_BIN2，直接通过字符的二进制编码判断唯一性：

CREATE TYPE [dbo].[String_Table] AS TABLE(
    [Value] [nvarchar](255) NOT NULL COLLATE SQL_Latin1_General_CP1_CS_AS_BIN2,
    PRIMARY KEY CLUSTERED 
(
    [Value] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)

内容的提问来源于stack exchange，提问作者boule