I am using SQL Server.

I have this table:

create table MyTable (
ID int not null ,
Name varchar(500) )

and I am adding a name_hash column to store hash values for the name column:

alter myTable
    name_hash AS HASHBYTES('SHA1', CONVERT(VARCHAR(90), Name))

How can I determine the resulting size of name_hash? Is it related to the size of the column that I want to apply the hashing to?


Well - it appears that a fellow Stack Exchange member (Richard Marskell - Drackir) has figured this out here.

I've copied his answer below:

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:

        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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.