[...] 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.