SQL Server : what is best datatype to store MD5 ha

2019-07-21 04:15发布

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 ?

0条回答
登录 后发表回答