How to Insert Md5 Hash Value in Ms SQL Server

Generate MD5 hash string with T-SQL

CONVERT(VARCHAR(32), HashBytes('MD5', 'email@dot.com'), 2)

How to create unique MD5 hash index on SQL Server?

It's your choice of the ISNULL replacement value that's tripping you up.

Run:

declare @t table (Manufacturer varchar(512), ManufacturerReference varchar(512))

select ISNULL(HashBytes('MD5',CONVERT(VARCHAR(512),
       CONCAT(Manufacturer,ManufacturerReference))), 'null')
from @t

And you get the error

Msg 257, Level 16, State 3, Line 4

Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

But run this query:

declare @t table (Manufacturer varchar(512), ManufacturerReference varchar(512))

select ISNULL(HashBytes('MD5',CONVERT(VARCHAR(512),
       CONCAT(Manufacturer,ManufacturerReference))), 0x) --not a string any more
from @t

And it runs without error

Storing MD5 Hash in SQL Server

Based on the documentation on MSDN and my experience, binary is better, since the md5 hash does not vary in size.

The size for a binary data type is n bytes, so the size of the data. The size of a varbinary data type is n bytes + 2 bytes on top of the size of the data.

Indexing with MD5 in SQL Server

I'm not sure what you are allowed to do schema-wise, but if you can modify the schema then SQL Server has something called Indexed Views, which are views that are stored in memory (vs computed on the fly).

You can query the view instead of the underlying table, and SQL Server will keep it all up-to-date for you. The key phrase is WITH SCHEMABINDING, which tells SQL Server to keep the computed fields in memory.

For example:

CREATE VIEW HashedAddresses
WITH SCHEMABINDING
AS
SELECT ID, MAIL, HASHBYTES('MD5',MAIL) as HashedMailMD5 from myschema.mytable;

Then you can create a unique clustered index on your hash field:

CREATE UNIQUE CLUSTERED INDEX IndexHashedAddresses ON HashedAddresses(HashedMailMD5);

after which this should be fast:

SELECT ID FROM HashedAddresses WHERE HashedMailMD5 = '0x121....'

Issue: if you get an MD5 collision, the index will fail. Not sure what to do about that...

SQL Server - Compare large number of MD5-Hashed Values

Check this answer. You need to compare varchar to varchar - not varbinary I think.

Generate MD5 hash string with T-SQL

---

Related Topics