What kind of datatype should one use to store hashes?

SqlSql ServerSql Server-2008Tsql

Sql Problem Overview


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 ??

Sql Solutions


Solution 1 - Sql

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionSuhrob SamievView Question on Stackoverflow
Solution 1 - SqlRichard Marskell - DrackirView Answer on Stackoverflow