Selecting with varbinary(max) criteria (in the where clause)
Just do 

SELECT ItemId
FROM   MyTable
WHERE  Value = 0xAAFF 


You are converting the varchar representation to varbinary(max) which is not correct.

SELECT CONVERT(VARBINARY(MAX), '0xAAFF') returns 0x307841414646 for me for example. It will give you a result based on the character codes in that string representation in your default collation's code page.