CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Suhrob Samiev
Suhrob Samiev

Reputation: 1547

What kind of datatype should one use to store hashes?

I understand that hashes will be different based on different datatypes in SQL Server. One support Unicode another not .... so on (also collation)

I am using char(32) as a datatype but the output is weird. Using this

select HASHBYTES('MD5','MD5Text')

gives this ouput:

0xA891DB2DA259280A66FD5F35201CAB6A

and when

declare @h char(32)
select @h=HASHBYTES('MD5','MD5Text')
select @h,LEN(@h)

output:

Ё‘Ы-ўY( fэ_5 «j

So I am new to SQL Server.
Could anyone, please, tell me what datatype should I use to store hashes ??

Upvotes: 67

Views: 65921

Answers (1)

Richard Marskell - Drackir
Richard Marskell - Drackir

Reputation: 13292

You should use the binary datatype. You can use binary instead of varbinary because the hash function will always return the same number of bytes for the same type of hash (e.g. MD5, SHA1, etc.). This will cut down on the (slight) overhead required to manage a variable length binary (varbinary) column.

In terms of what size to make it, you can run this query to check the length of each hash type:

SELECT  DATALENGTH(HASHBYTES('MD2', 'Testing')) AS [MD2Length],
        DATALENGTH(HASHBYTES('MD4', 'Testing')) AS [MD4Length],
        DATALENGTH(HASHBYTES('MD5', 'Testing')) AS [MD5Length],
        DATALENGTH(HASHBYTES('SHA', 'Testing')) AS [SHALength],
        DATALENGTH(HASHBYTES('SHA1', 'Testing')) AS [SHA1Length],
        /* 2012 only: */
        DATALENGTH(HASHBYTES('SHA2_256', 'Testing')) AS [SHA2_256Length],
        DATALENGTH(HASHBYTES('SHA2_512', 'Testing')) AS [SHA2_512Length];

And it should come out with this:

MD2Length MD4Length MD5Length SHALength SHA1Length SHA2_256Length SHA2_512Length
--------- --------- --------- --------- ---------- -------------- --------------
16        16        16        20        20         32             64

Upvotes: 155

Related Questions