Generate a hash for a set of rows in sql server

Question

Is there any way in SQL Server 2012 to generate a hash of a set of rows and columns?

I want to generate a hash, store it on the parent record. The when an update comes in, I'll compare the incoming hash with the parent record hash and I'll know whether the data has changed.

So something like this would be nice:

SELECT GENERATEHASH(CONCATENATE(Name, Description, AnotherColumn))
FROM MyChildTable WHERE ParentId = 2 -- subset of data belong to parent record 2

"CONCATENATE" would be an aggregate function which would not only concat the columns, but also, the rows inside the resultset. Like MAX, but returning everything as a string concatenation.

Hopefully this helps you see what I mean anyway!

The fundamental problem I'm trying to solve is that my client's system perform imports of vast amounts of hierarchical data. If I can avoid processing through the use of hashes, then I would think this will save a lot of time. At the moment, the SP is running 300% slower when having to process duplicate data.

Many thanks

score 11 · Answer 1 · edited Mar 07 '14 at 13:49

11

select HashBytes('md5',convert(varbinary(max),(SELECT * FROM MyChildTable WHERE ParentId = 2 FOR XML AUTO)))

but HashBytes is limited to 8000 bytes only... you can make a function to get de Md5 for every 8000 bytes....

edited Mar 07 '14 at 13:49

Micha

5,117
8
34
47

answered Mar 07 '14 at 13:34

jrionegro

111
1
2

1

If you are on SQL Server 2016 or above, which has some JSON support, I recommend using `FOR JSON AUTO` instead of `FOR XML AUTO`, as this seems to be about 2x faster in a few tests I did. – Isak Aug 01 '17 at 15:28
1

Note: limit of 8000 bytes is for SQL 2014 and before. https://learn.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15 – Brett Manners May 12 '22 at 03:41

score 10 · Accepted Answer · answered Aug 08 '12 at 11:16

10

You can use the CHECKSUM_AGG aggregate. it is made for that purpose.

answered Aug 08 '12 at 11:16

usr

168,620
35
240
369

5

Unfortunately CHECKSUM has known weaknesses (i.e. practical collisions). E.g. decimal type http://sqlserverpains.blogspot.com.au/2008/06/checksum-pains.html so just beware. – Shiv Mar 31 '15 at 05:45

juergen d · Answer 3 · 2014-01-11T20:56:37.073

2

For single row hashes:

select HASHBYTES('md5', Name + Description + AnotherColumn)
FROM MyChildTable WHERE ParentId = 2

for table checksum:

select sum(checksum(Name + Description + AnotherColumn)*1.0)
FROM MyChildTable WHERE ParentId = 2

edited Jan 11 '14 at 20:56

answered Aug 08 '12 at 10:55

juergen d

201,996
37
293
362

Does this produce a hash from the entire resultset? Or will it produce multiple hashes, one for each row in MyChildTable? – krisdyson Aug 08 '12 at 10:57
Thanks for the idea @juergend. Going to use the checksum solution a lot. – Ben Gripka Jan 11 '14 at 20:56

score 1 · Answer 4 · answered Oct 10 '13 at 12:40

Another approach:

-- compute a single hash value for all rows of a table
begin

    set nocount on;

    -- init hash variable
    declare @tblhash varchar(40);
    set @tblhash = 'start';

    -- compute a single hash value
    select @tblhash = sys.fn_varbintohexsubstring(0, hashbytes('sha1',(convert(varbinary(max),@tblhash+
    (select sys.fn_varbintohexsubstring(0,hashbytes('sha1',(convert(varbinary(max),
    -- replace 'select *' if you want only specific columns to be included in the hash calculation
    -- [target table] is the name of the table to calc the hash from
    -- [row_id] is the primary key column within the target table
    -- modify those in the next lines to suit your needs:
    (select * from [target_table] obj2 where obj2.[row_id]=obj1.[row_id] for xml raw)
    ))),1,0))
    ))),1,0)
    from [target_table] obj1;

    set nocount off;

    -- return result
    select @tblhash as hashvalue;

end;

Generate a hash for a set of rows in sql server

4 Answers4

Linked