3

I want to apply it to procedure definitions.

My basic idea is to split the string of type nvarchar(max) into chunks of nvachar(4000) and concatenate the results of HashBytes('MD5',chunk).

I can't imagine that this is not done yet.

  • Native support for longer since SQL2016: "For SQL Server 2014 (12.x) and earlier, allowed input values are limited to 8000 bytes." – Karl Kieninger Oct 30 '19 at 1:49
5

If you insist, then

CREATE FUNCTION dbo.GetMyLongHash(@data VARBINARY(MAX))
RETURNS VARBINARY(MAX)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    DECLARE @res VARBINARY(MAX) = 0x
    DECLARE @position INT = 1, @len INT = DATALENGTH(@data)

    WHILE 1 = 1
    BEGIN
        SET @res = @res + HASHBYTES('MD5', SUBSTRING(@data, @position, 8000))
        SET @position = @position+8000
        IF @Position > @len 
          BREAK
    END
    RETURN @res
END

BUT may be better to use this CLR function and hash data of any length into real MD5 hash?

using System;
using System.Data.SqlTypes;
using System.IO;

namespace ClrHelpers
{
    public partial class UserDefinedFunctions {
        [Microsoft.SqlServer.Server.SqlFunction]
        public static Guid HashMD5(SqlBytes data) {
            System.Security.Cryptography.MD5CryptoServiceProvider md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
            md5.Initialize();
            int len = 0;
            byte[] b = new byte[8192];
            Stream s = data.Stream;
            do {
                len = s.Read(b, 0, 8192);
                md5.TransformBlock(b, 0, len, b, 0);
            } while(len > 0);
            md5.TransformFinalBlock(b, 0, 0);
            Guid g = new Guid(md5.Hash);
            return g;
        }
    };
}
  • 1
    Your solution requires a cast like, Select dbo.GetMyLongHash(cast(definition as varbinary(max))) from sys.sql_modules where object_id = object_id('<myproc>'), but that is a little inconvenience for keeping it more general. Yes and your version regards differences in trailing blanks. – bernd_k Jan 4 '12 at 12:14

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.