4

We face a very weird issue.

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

  1. userId - int
  2. userName - varbinary(256)
  3. 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 ?

MoShe
  • 6,197
  • 17
  • 51
  • 77
  • Have you posted the exact usernames that cause the problem. I cannot replicate this error using your values. http://sqlfiddle.com/#!3/2faf2/1 – GarethD Apr 25 '13 at 09:19
  • 2
    @GarethD You're not doing it properly. You're casting a string to a varbinary. If you look at your fiddle result (first select), you can see two `,48,48` which are two bytes with the ordinal value 48 representing the ASCII character "0". Those are not the byte 0x00. Try this instead `INSERT T VALUES (0x5942C803664B00),(0x5942C803664B);` – RichardTheKiwi Apr 25 '13 at 09:24
  • Wow. Apparently I need more coffee, and to probably double check everything I have done this morning because that was a pretty basic error... Whoops. – GarethD Apr 25 '13 at 09:52

1 Answers1

6

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
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • 1
    but 0x5942C803664B and 0x5942C803664B00 are different number... what shout be the column type the will not ignore this? I will understand that 0x5942C803664B and 0x005942C803664B are the same? – MoShe Apr 25 '13 at 09:12