HASHBYTES – A T-SQL Function

Someone was asking if the HASHBYTES function was a good one to use in T-SQL as far as performance goes.. I wish I had a good reference for the function, but the best one I had on SQLServerCentral was this piece on using it to load a data warehouse. I also wrote an editorial on it not working with strings beyond 8k, which seems to be a bug, or a lack of resources devoted to ensuring string functions work with varchar(max).

The HASHBYTES function returns a hash of an input string. A hash is essential a calculation based on the values of the input, and two inputs that are the same, ought to produce the same hash. One catch with this function is that you provide the algorithm used, which can be one of these:

  • MD2
  • MD4
  • MD5
  • SHA
  • SHA1

Each of these produces different output, returning a varbinary(max) value. As an example, suppose I hash “Steve Jones”

SELECT HASHBYTES('MD2', 'Steve Jones') 'MD2'
UNION
SELECT HASHBYTES('MD4', 'Steve Jones') 'MD4'
UNION
SELECT HASHBYTES('MD5', 'Steve Jones') 'MD5'
UNION
SELECT HASHBYTES('SHA', 'Steve Jones') 'SHA'
UNION
SELECT HASHBYTES('SHA', 'Steve Jones') 'SHA1'

The results look like this:

MD2

———————————————-

0x27851A666BFCB4A35F971DD742CDA15F

0x2E978DE4841B1F3651A8DF4B2D2CF5F5C624A76B

0x75931813C7EAAEAB3CD1D8D621935903

0x979AC597C05CA6DE3A88C31A456D1125

As you can see, there’s a different hash for the same value using different algorithms. However if I were to compare the same string to itself, I can easily tell if something has changed. If the hashes aren’t the same, there’s a difference. I’m not sure this is a great use, but the more obvious use is that I can hash a password and then have the user enter their own version, hash it, and compare the results. In this way, the system never needs to know the value.

Just make sure you use the same algorithm Winking smile

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

2 Responses to HASHBYTES – A T-SQL Function

  1. John Magnabosco says:

    I have a chapter in my book, “Protecting SQL Server Data”, where I discuss one-way encryption and the use of Hashbytes.

    Like

  2. As an aside, people shouldn’t be using any of these algorithms for hashing passwords (see http://codahale.com/how-to-safely-store-a-password/).

    Otherwise, HASHBYTES is great for generating a unique signature for any large piece of data that we’d rather not compare in their entirety.

    Like

Comments are closed.