HASHBYTES encrytion length and quering issue

Question

I am running the following query to encrypt certain value:

declare @peoplekey int
set @peoplekey = 500

select HASHBYTES('SHA2_256',cast(@peoplekey as varbinary(2000)))

The output is : 0xDC28C75DEDB09C0B0510B97BC59F879E7741BA2A396CBAA430623ED4CEAA0EF6

But when i am explicitly trying to get the length of the query output vs the actual output, i am getting two different values:

select len(HASHBYTES('SHA2_256',cast(@peoplekey as varbinary(2000))))

select len('0xDC28C75DEDB09C0B0510B97BC59F879E7741BA2A396CBAA430623ED4CEAA0EF6')

Why lengths of both these statements are different?

Also, i have inserted the above encrypted value in a table using this statement:
insert into #test
select @peoplekey,HASHBYTES('SHA2_256',convert(varbinary,@peoplekey))

And the data is getting inserted correctly. But when i am trying to filter data using this varbinary value i get 0 rows in the output

select * from #test where c2 = '0xDC28C75DEDB09C0B0510B97BC59F879E7741BA2A396CBAA430623ED4CEAA0EF6'

How can we query varbinary data present in the table?

Answer 1

[...] but when i am explicitly trying to get the length of the query output vs the actual output, i am getting two different values:

That's because you are treating the binary value as a string value, so it will be double the size (UCS2) + the size of "0x" that is prepended. Consider the following in your situation:

declare @peoplekey int
set @peoplekey = 500

select len(HASHBYTES('SHA2_256',cast(@peoplekey as varbinary(2000))))
select len('0xDC28C75DEDB09C0B0510B97BC59F879E7741BA2A396CBAA430623ED4CEAA0EF6')
select len(0xDC28C75DEDB09C0B0510B97BC59F879E7741BA2A396CBAA430623ED4CEAA0EF6)

Notice on the last one (I added, not in your original) I treat the value as a binary, not a string.