SQL Server : what is best datatype to store MD5 hash?
Asked Answered
T

0

4

I need to store in SQL Server a list of items uniquely identified by a hash.

In C# I compute my hash as MD5 hash of concatenation of the item ids ( Guid ).

Currently, my hash has datatype byte[] in C# , and binary(16) in SQL Server.

Here's the DDL :

CREATE TABLE [dbo].[ItemSet](
    [GUID] [uniqueidentifier] NOT NULL,
    [HashCode] [binary](16) NOT NULL,
    [Item1GUID] [uniqueidentifier] NOT NULL,
    [Item2GUID] [uniqueidentifier] NOT NULL,
    ...
    [UtcModified] [datetime] NULL,
 CONSTRAINT [ItemSet_PK] PRIMARY KEY CLUSTERED 
(
    [GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

But perhaps I am better with :

[HashCode] [uniqueidentifer] NOT NULL,

uniqueidentifier is also 16 bytes and perhaps SQL Server offers performance or other advantages for uniqueidentifier over binary(16) ?

In C# the corresponding datatype is Guid and apparently there's a special Type 3 Guid form when calculated from an MD5 hash :

nnnnnnnn-nnnn-3nnn-xnnn-nnnnnnnnnnnn

where 3 indicates Type 3 , and the x byte is masked to 10xx.

But it's not clear to me how to create such a Type 3 Guid. And for my purposes it seems artificial & unnecessary to utilize a special format datatype.

Is there any optimal/accepted standard for working with MD5 hashes in C# and T-SQL ?

Taken answered 10/4, 2018 at 19:18 Comment(3)
Possible duplicate of How can I store a hash in a SQL Server database using C#?Safford
Check out this very thorough answer on dba.stackexchange.comAbjure
note this is not a duplicate of posts asking whether to store hash in db character vs numeric datatypes. i'm not asking about char(n) varchar nchar(n) nvarchar.Taken

© 2022 - 2024 — McMap. All rights reserved.