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.
0x20 = 0x2020
is false, though. Trailing spaces are observed.