Hashing more than 8000 bytes in SQL Server

Question

The SQL Server HASHBYTES hash function has an input limit of 8,000 bytes.

How do you make more stringent lines?

Answer 1

You can use an 8k hash (or 4k or 2k) of input fragments, and then either combine these hashes or translate them into a new hash value. This can be difficult, although if you need to create a similar algorithm (for example, for an external .NET application) to compare hashes created outside of SQL Server.

Another option: Lean on SQL Server CLR integration and hash in the .NET assembly.

Answer 2

You can write a SQL CLR function:

 [Microsoft.SqlServer.Server.SqlFunction] public static SqlBinary BigHashBytes(SqlString algorithm, SqlString data) { var algo = HashAlgorithm.Create(algorithm.Value); var bytes = Encoding.UTF8.GetBytes(data.Value); return new SqlBinary(algo.ComputeHash(bytes)); } 

And then it can be called in SQL as follows:

 --these return the same value select HASHBYTES('md5', 'test stuff') select dbo.BigHashBytes('md5', 'test stuff') 

BigHashBytes is only required if the length is more than 8k.

Answer 3

As Paul’s idea, one idea that comes to mind for chunking is to store the hashed string in an XML column, with each fragment being a separate XML element.