Home » Blog » HASHBYTES: How to Calculate a Cryptographic Hash in T-SQL

HASHBYTES: How to Calculate a Cryptographic Hash in T-SQL

2014-07-07 - Cryptography, General, Security

Introduction

Cryptographic hash functions can be used in many situations, e.g. to store passwords securely or to provide a searchable key for encrypted values. If you have to calculate a hash value in the application layer, you can use one of the libraries available in your language of choice. Sometimes however you have to calculate the hash value in T-SQL. For those cases, SQL Server provides the HASHBYTES function.

A HASHBYTES Example

The HASHBYTES function takes two parameters. The first parameter is the name of the algorithm to be used. The second parameter is the string that is used as input for the hash function:

[sql] DECLARE @AString NVARCHAR(MAX) = 'Hello World';

SELECT HASHBYTES('SHA2_512',@AString) AS [@AString (1)];
[/sql]

The hash calculation does not automatically introduce a salt, but rather does a straightforward hash value calculation. That means that the same input value will always produce the same output value:

HASHBYTES Limitations

There are two limitations to the HASHBYTES function that you need to be aware of. The first limitation is that there is only a fairly small selection of available hash algorithms:

SHA2_512
SHA2_256
SHA1
SHA
MD5
MD4
MD2

All algorithms provided are all based on cryptographic hash functions. Because of that, any howsoever small change in the input value will likely change all output bytes:

However, only the two algorithms from the SHA2 family can still be considered secure. The other algorithms are provided for legacy applications only.

The second limitation is the maximum length of the input value. While, as you can see in the example above, NVARCHAR(MAX) is a valid input data type, any input value that takes more than 8000 bytes (not characters) will cause an error:

However, as long as you stay under the 8000-byte limit, you can use any character or binary data type as input.

Summary

There are situations that require calculating a hash value within T-SQL. The HASHBYTES T-SQL function, while somewhat limited, does provide the most common hash algorithms and is simple to use as long as the input value is no longer than 8000 bytes.