It seems that SQL Server considers 0x and 0x00 equal values:
SELECT CASE WHEN 0x = 0x00 THEN 1 ELSE 0 END
This outputs 1
.
How can I get true binary bit-for-bit comparison behavior? Also, what are the exact rules under which two (var)binary
values are considered equal?
Also note the following behavior:
--prints just one of the values
SELECT DISTINCT [Data]
FROM (VALUES (0x), (0x00), (0x0000)) x([Data])
--prints the obvious length values 1, 2 and 3
SELECT DATALENGTH([Data]) AS [DATALENGTH], LEN([Data]) AS [LEN]
FROM (VALUES (0x), (0x00), (0x0000)) x([Data])
Background of the question is that I'm trying to deduplicate binary data. I need to GROUP BY
binary data, not just compare two values. I'm glad I even noticed this problem.
Note, that HASHBYTES
does not support LOBs. I'd also like to find a simpler solution.
Best Answer
I couldn't find this comparison behaviour specified anywhere in BOL.
But the Connect Item Invalid equality comparison for varbinary data with right padded zeros states that
The Connect Item also states that the presence of trailing zeroes is the only case in which SQL Server differs from byte-by-byte comparison behavior.
In order to distinguish between two binary values in SQL Server that differ only by trailing
0x00
characters you can also addDATALENGTH
into the comparison as indicated in your question.The reason for preferring
DATALENGTH
rather thanLEN
generally here is because the latter gives an implicit cast tovarchar
and then you get the problem with trailing spaces.Though either would work in your use case.