Duplicate issue when inserint value for varbinary

Question

We are facing a very strange problem.

we have one table in our mssql 2008 R2 db when the table column is as follows:

• userId - int
• username is varbinary (256)
• userType - int

Column

and userName is unique

we again prepare the following table query:

insert into table_name (userId, userName, userType) values ( 1 , 0x5942C803664B00, 0) 

and after this request we will execute the following request:

 insert into table_name (userId, userName, userType) values ( 2 , 0x5942C803664B, 0) 

and we get the following error:

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

although 0x5942C803664B and 0x5942C803664B00 are different values

Any idea?

Answer 1

The trailing 0x00 null bytes in the varbinary column are as small as the trailing spaces in the varchar column. Therefore, your values ​​are actually duplicated.

In other words, your two values ​​(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 0) is considered insignificant for him. This is for 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 trailing null bytes significant, you can spoof and add something the same for 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