Q:

duplicate issue when inserint value to varbinary

We face a very weird issue.
we have one table in our mssql 2008 R2 db  when table column is the follow:

userId - int
userName - varbinary(256)
userType - int

and userName column  is unique 
we preform the following query again the table:
insert into table_name (userId, userName, userType) values ( 1 ,  0x5942C803664B00, 0)

and after that query we do the following query :
insert into table_name (userId, userName, userType) values ( 2 ,  0x5942C803664B, 0)

and we get the following Error:

Cannot insert duplicate key row in object 'table_name ' with unique index 'table_name _userName_u'.

although  0x5942C803664B and 0x5942C803664B00 are different values??
Any idea ?

A:

The trailing "zero-bytes" 0x00 in a varbinary column is as insignificant as the trailing spaces " " in a varchar column.  Therefore, your values are actually duplicates.
In other words, your two values are (in byte order)
1  2  3  4  5  6  7  --- bytes in the binary value
59 42 C8 03 66 4B
59 42 C8 03 66 4B 00

The last byte (8 bits of 0) is considered insignificant for the purposes of comparison.  This is the same reason why you get
select CASE WHEN 'abc ' = 'abc' then 'SAME' else 'DIFFERENT' end
-- select case when 0x5942C803664B = 0x5942C803664B00 then 'same' else 'different' end

result
======
SAME

To make the trailing zero-bytes significant, you can cheat and add something equally to both parts.
select case when 0x5942C803664B + 0xff = 0x5942C803664B00 + 0xff
            then 'same'
            else 'different' end   -- different

select case when 0x5942C80366AA + 0xff = 0x5942C80366AA + 0xff
            then 'same'
            else 'different' end   -- same

