It seems that SQL Server considers 0x and 0x00 equal values:
SELECT CASE WHEN 0x = 0x00 THEN 1 ELSE 0 END
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.
HASHBYTES does not support LOBs. I'd also like to find a simpler solution.