2

We'd like to use MD5 for our hashing function instead of SHA_256 but as of SQL Server 2016, MD5 is deprecated. We're using this for hashing (comparing which records have changed). We now have this dilemma of using of risking it by using this function or incurring storage and performance overhead of using SHA_256. It's frustrating Microsoft decided to deprecate these functions even though they are still useful in certain scenarios.

This project isn't a critical component of the business. We'll likely go with SHA_256 but is this the right choice? Should new development always avoid deprecated functions?

For context - daily will be about 1-2 million upserting into a 400 million row table comparing hashbytes on the fly. about 30 columns wide

https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-2017

Choosing the right algorithm in HashBytes function

  • If you are working with a data vault, see very relevant a question. – vonPryz Feb 7 at 14:52
  • How many rows are you comparing? – Max Vernon Feb 7 at 19:27
  • @MaxVernon daily will be about 1-2 million upserting into a 400 million row table comparing hashbytes on the fly. – Gabe Feb 7 at 20:38
  • 2
    Gabe: as @vonPryz mentioned, there is another question on this same topic currently being investigated right now. If you are able to use SQLCLR, there are 2 options being tested in that other question. One of them is a library that I created, SQL#, and the Free version contains Util_HashBinary that can do MD5. If you are open to using an algorithm besides MD5, Paul White's answer to that question suggests using a custom hash, Spooky, which is 128 bits like MD5, but appears to be faster than MD5 and SHA256. – Solomon Rutzky Feb 8 at 16:14
1

From Microsoft's Documentation:

When a feature is marked deprecated, it means:

  • The feature is in maintenance mode only. No new changes will be done, including those related to inter-operability with new features.
  • We strive not to remove a deprecated feature from future releases to make upgrades easier. However, under rare situations, we may choose to permanently remove the feature from SQL Server if it limits future innovations.
  • For new development work, we do not recommend using deprecated features.

Another part of that page says:

The following SQL Server Database Engine features are supported in the next version of SQL Server, but will be deprecated in a later version. The specific version of SQL Server has not been determined.

Having said that, MD2, MD4, MD5, SHA, and SHA1 algorithms are not available under compatibility level 130.

So, for new development work, you should certainly stay away from those hash algorithms since they will prevent you using all the latest-and-greatest features available with SQL Server 2017+.

Depending on how you're using MD5 and HASHBYTES, you may have other options available aside from using SHA_256.

You may be interested in using this methodology I documented to determine which rows have changes. It uses a rowversion column in the source table to allow you to instantly see rows that are new or have changes. This avoids using a deprecated feature, and is much faster than any hashing function will ever be.

  • Thanks for the reply. Regarding those algorithms not available in 130, that's not accurate. I'm on compatibility level 140 and MD5 and sha1 are working. Unless I'm misunderstanding? – Gabe Feb 7 at 20:40
  • My statement is just based on the documentation; perhaps that means it won't be supported on 130 in SQL Server 2019. – Max Vernon Feb 8 at 0:52

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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