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 ?
