Getting different results for HASHBYTES() method in SQL Server

Question

I am trying to use HASHBYTES with MD5 algo. but getting different results depending upon how the data is passed i.e.,

1. if passed using a variable SELECT HASHBYTES('MD5', @Var) shows the result 0xBEC062C01D70F0D2FC15F2E43EE3A66B

2. if passed direclty SELECT HASHBYTES('MD5', 'asd123') shows the result 0xBFD59291E825B5F2BBF1EB76569F8FE7

I inserted the data using INSERT but when I try to retrieve using my STORED PROCEDURE, no records are displayed.

Is there something I'm missing?

Answer 1

It has to do with character encodings. Your @Var variable is declared as NVARCHAR (which is encoded as UTF-16 Little Endian) but the string literal is VARCHAR (encoded using the 8-bit code page associated with the current DB's default collation) since it's not prefixed with an upper-case N.

SELECT HASHBYTES('MD5', 'asd123') AS [VARCHAR],
       HASHBYTES('MD5', N'asd123') AS [NVARCHAR];

Returns:

VARCHAR                             NVARCHAR
----------------------------------  ----------------------------------
0xBFD59291E825B5F2BBF1EB76569F8FE7  0xBEC062C01D70F0D2FC15F2E43EE3A66B

For a fully detailed explanation, please see my answer here:

TSQL md5 hash different to C# .NET md5