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

Micha · Accepted Answer · 2014-03-07 13:49:52Z

12

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 7, 2014 at 13:49

Micha

5,1838 gold badges36 silver badges47 bronze badges

answered Mar 7, 2014 at 13:34

jrionegro

1211 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Isak Over a year ago

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.

Brett Manners Over a year ago

Note: limit of 8000 bytes is for SQL 2014 and before. learn.microsoft.com/en-us/sql/t-sql/functions/…

usr · Accepted Answer · 2012-08-08 11:16:22Z

10

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

answered Aug 8, 2012 at 11:16

usr

172k38 gold badges251 silver badges380 bronze badges

1 Comment

Shiv Over a year ago

Unfortunately CHECKSUM has known weaknesses (i.e. practical collisions). E.g. decimal type sqlserverpains.blogspot.com.au/2008/06/checksum-pains.html so just beware.

juergen d · Accepted Answer · 2014-01-11 20:56:37Z

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, 2014 at 20:56

answered Aug 8, 2012 at 10:55

juergen d

205k40 gold badges305 silver badges377 bronze badges

2 Comments

krisdyson Over a year ago

Does this produce a hash from the entire resultset? Or will it produce multiple hashes, one for each row in MyChildTable?

Ben Gripka Over a year ago

Thanks for the idea @juergend. Going to use the checksum solution a lot.

StackDex · Accepted Answer · 2013-10-10 12:40:50Z

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;

Collectives™ on Stack Overflow

Generate a hash for a set of rows in sql server

4 Answers 4

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related