sql-server - SQL Server varbinary trailing 0 解释

Question

CREATE TABLE [sql_table1] ([c0] varbinary(25) NOT NULL primary key) 
go

insert into sql_table1 values (0x3200),(0x32);
go

我明白了

Cannot insert duplicate key in object 'dbo.sql_table'. The duplicate key value is (0x32).

为什么？ 0x32 不等于 0x3200

Answer 1

它得到了正确的填充。 BINARY 当您尝试指定通常应该等效的十六进制数值时，数据会变得棘手。如果您尝试这样做，它将起作用:

insert into #sql_table1 values (0x32),(CAST(50 as VARBINARY(25)));
-- inserts 0x00000032
-- and     0x32

但这些在数值上是等价的。一般来说，将任何类型的 BINARY 列作为主键或尝试在其上放置唯一索引(比 CHAR/VARCHAR/NVARCHAR 列)- 插入其中的任何应用程序几乎肯定会从某些 native 格式/表示形式CAST转换为二进制，但是无法保证 CAST 实际上在任一方向上都以独特的方式工作 - 应用程序是否插入了值 50 (= 0x32)，或者它尝试插入文字 0x32，或者它是否尝试插入 2 (= 0x32) 的 ASCII 值，或者它是否插入了其他东西的第一个字节......？如果一个应用程序提交 0x32 和另一个 0x0032 它们是相同的还是不同的(SQL Server 说不同 - 见下文)？

底线是，如果您尝试在没有任何上下文的情况下比较二进制列，SQL Server 将表现异常。可行的方法是使用 BINARY_CHECKSUM

比较二进制数据

SELECT BINARY_CHECKSUM(0x32)  -- 50
SELECT BINARY_CHECKSUM(0x320) -- 16! -- it's treating this as having a different number or ordering of bytes
SELECT BINARY_CHECKSUM(0x3200) -- 50
SELECT BINARY_CHECKSUM(0x32000) -- 16
SELECT BINARY_CHECKSUM(0x0032) -- 50
SELECT BINARY_CHECKSUM(0x00032) -- 50
SELECT BINARY_CHECKSUM(0x000000000032)  -- 50

但同样，这只会帮助您了解二进制数据的十六进制表示不会完全按照它看起来的方式工作。关键是，您的主键将基于数据的 BINARY_CHECKSUM，而不是数据的任何特定格式/表示。通常这是一件好事，但对于二进制数据(和填充)，它变得更加棘手。即便如此，在我上面的示例中，两列的 BINARY_CHECKSUM 将完全相同(SELECT BINARY_CHECKSUM(c0) FROM sql_table1 将为 50 输出两行)。奇怪 - 进一步的测试表明，任何不同数量的适合列长度的前导 0 将绕过唯一检查，即使校验和相同(例如 VALUES (0x32), (0x032), (0x0032) 等)。

如果您开始混合使用不同版本的 SQL Server(根据 MSDN documentation)，情况只会变得更糟。

对于表上的 PK/独特设计，您应该做的是找出什么上下文可以理解此数据 - 订单号、文件引用、时间戳、设备 ID、其他一些业务或逻辑标识符等....如果不出意外，使用 IDENTITY 列伪键。