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 ?
char(n)
varchar
nchar(n)
nvarchar
. – Taken