Why generated MD5 hash in sql server are not equal?
c#, hash, md5, sql-server
Solution
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
Problem
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!?