Why generated MD5 hash in sql server are not equal

Question

This question already has an answer here:

• TSQL md5 hash different to C# .NET md5 4 answers

I have a table in SQL Server 2008 R2 that contain two field (WordHash, Word). This Hash field generated in C# and I need regenerate hash code for Word field in sql server.

But my problem is that generated MD5 hash in sql server and C# are different. I found below code to resolve this problem but still I have same problem.

SQL code:

CONVERT(NVARCHAR(32),HASHBYTES('MD5', 'some word'), 2)

After putting this code block to my query, I saw some wired result! This is my result:
My Query:

SELECT 
    [WordHash],
    convert(nvarchar(32),HASHBYTES('MD5', 'Analytics'),2) AS TestHash,
    convert(nvarchar(32),HASHBYTES('MD5', [Word]),2) AS SqlHash
FROM myTable

Result:

WordHash: A768CAA988605A2846599CF7E2D0C26A
TestHash: A768CAA988605A2846599CF7E2D0C26A
SqlHash F4AFA5FEF805F7F5163EC6402BAF61FF

Note that the 'Analytics' is one of records data in database.
Why TestHash & SqlHash are different while they generated from same code!?

Answer 1

The issue is NVARCHAR and VARCHAR get hashed to different values. Both HASHBYTES('MD5', 'Analytics'), and [WordHash] are hashes of VARCHAR values but [Word] is a NVARCHAR.

select HASHBYTES('MD5',  'Analytics'), 'varchar'
union
select HASHBYTES('MD5', N'Analytics'), 'nvarchar'

--outputs
------------------------------------- --------
0xA768CAA988605A2846599CF7E2D0C26A    varchar
0xF4AFA5FEF805F7F5163EC6402BAF61FF    nvarchar

To fix this you must either change [Word] to be VARCHAR or re-compute [WordHash] using NVARCHAR values.

Some useful further reading: Comparing SQL Server HASHBYTES function and .Net hashing