1

I want to aggregate a single HASHBYTES() from the results of another HASHBYTES()... something like CHECKSUM_AGG() does for CHECKSUM()...

I have been able find how to make a single HASH per row of columns from a table:

SELECT 
    hashbytes('MD5', (
            SELECT SPECIFIC_SCHEMA
                ,SPECIFIC_NAME
                ,ROUTINE_SCHEMA
                ,ROUTINE_NAME
                ,ROUTINE_TYPE
                ,ROUTINE_DEFINITION
            FROM (
                VALUES (NULL)
                ) foo(bar)
            FOR XML auto
            )) AS [Hash]
FROM INFORMATION_SCHEMA.ROUTINES AS MBT;

How do I aggregate to a single hash from all the hash rows returned?

(Yes, I want to reduce a table to a single hash for comparison to table(s) in other databases).

1 Answer 1

1

You can use CONCAT to concatenate all the columns per row, then STRING_AGG to concatenate the whole table, and hash the result.

How reliable such a hash would be in the face of collisions, I couldn't say. MD5 is deprecated anyway, you should use SHA2_512

SELECT 
    HASHBYTES('SHA2_512',
      STRING_AGG(CONCAT(
        CAST(SPECIFIC_SCHEMA AS nvarchar(max))  -- at least one '(max)'
       ,SPECIFIC_NAME
       ,ROUTINE_SCHEMA
       ,ROUTINE_NAME
       ,ROUTINE_TYPE
       ,ROUTINE_DEFINITION
       ), '')) AS [hash]
FROM INFORMATION_SCHEMA.ROUTINES AS MBT;

db<>fiddle

Why you are using INFORMATION_SCHEMA.ROUTINES as opposed to sys.procedures is a different question. Is it just an example?

2
  • it is just a useful example... I am trying to write a HASHBYTES() version of the CHECKSUM()/CHECKSUM_AGG() script I like from stackoverflow.com/questions/4311969/… Jul 22, 2022 at 0:15
  • The STRING_AGG needs a WITHIN_GROUP ORDER BY <something unique> to produce a deterministic hash. Also, if the table is large, this solution ends up building an extremely large string in memory.
    – Paul White
    May 31, 2023 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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