How to use HASHBYTES function in SQL Server for multiple columns

Question

I have a requirement wherein I have to create hashvalue which consist of all columns of a table. With Checksum this can be done easily, but Checksum is not recommended as per Microsoft:

If at least one of the values in the expression list changes, the list checksum will probably change. However, this is not guaranteed. Therefore, to detect whether values have changed, we recommend the use of CHECKSUM only if your application can tolerate an occasional missed change. Otherwise, consider using HashBytes instead. With a specified MD5 hash algorithm, the probability that HashBytes will return the same result, for two different inputs, is much lower compared to CHECKSUM.

HASHBYTES accepts only 2 parameters (algorithm type, column)

Now the problem is even though HASHBYTES is more reliable compared to checksum but there doesn't seem to be an easy way to create it on multiple columns.

An example in the checksum,

create table dbo.chksum_demo1
(
    id int not null,
    name varchar(25),
    address varchar(250),
    HashValue as Checksum (id,name,address)
    CONSTRAINT PK_chksum_demo1 PRIMARY KEY (Id)
)

How can we do the above using Hashbytes instead of checksum?

There are many ways here sqlservercentral.com/Forums/1725965/… — Pavan Chandaka
– Pavan Chandaka, Commented Sep 24, 2018 at 20:16

Brian · Accepted Answer · 2018-09-24 20:05:03Z

12

One method is concat() the fields together along with a delimiter. For any dates format them to strings manually to control the formatting.

 HashValue as HASHBYTES('SHA2_256', CONCAT(ID,'|',name,'|',address))

The delimiter is needed to handle empty fields so ID:1 Name:'' Address:'12' is different from ID:1 Name:'12' Address:''.

edited Sep 24, 2018 at 20:05

answered Sep 24, 2018 at 19:40

Brian

7,4502 gold badges30 silver badges37 bronze badges

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

2 Comments

James Hill Over a year ago

Why is the delimiter necessary, or is it optional?

Rudolph561 Jun 20 at 0:48

After concatenating, the hash may be the same for two different rows. Think Col A = 1 and B = 10 vs A = 11 and B = 0. They will both boil down to HASHBYTES('SHA2_256', '110'), versus with a delimiter, HASHBYTES('SHA2_256', '1|10') and HASHBYTES('SHA2_256', '11|0')

Don Pedro Alvares · Accepted Answer · 2020-08-04 22:45:02Z

11

Use this:

SELECT *,    
HASHBYTES('MD5', (SELECT ID, name, address FOR XML RAW))
FROM Table1

answered Aug 4, 2020 at 22:45

Don Pedro Alvares

1111 silver badge2 bronze badges

1 Comment

rrrrrrrrrrrrrrrr Over a year ago

Do either XML or Microsoft give any guarantees upon the stability of the output of "FOR XML RAW"? While an hash might generate collisions (all hashes do, as a mapping from a bigger to a smaller cardinality), the same input should always produce the same output. Some things that in my mind might change in the output, while giving an equivalent XML document (and thus being a valid and correct output of the query), are the ordering of attributes, spacing, quoting, etc.

Jon Crowell · Accepted Answer · 2021-04-19 16:28:04Z

11

SELECT HASHBYTES('<algorithm>', CONCAT_WS('|', f1, f2, f3, f4 ...))
FROM Table1

algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512

edited Apr 19, 2021 at 16:28

Jon Crowell

22.4k15 gold badges94 silver badges113 bronze badges

answered Jul 22, 2020 at 1:12

Oleg

1,8454 gold badges28 silver badges40 bronze badges

Comments

Nick.Mc · Accepted Answer · 2021-08-19 11:04:19Z

4

Copying the best suggestion from the supplied link here, and adding a where to show it can be used:

select MBT.refID,
hashbytes(
    'MD5',
    (select MBT.* from (values(null))foo(bar) for xml auto)
) as [Hash]
from MyBaseTable as MBT
where MBT.SomeFilter='X'

https://www.sqlservercentral.com/forums/topic/suggestionsolution-for-using-hashbytes-across-entire-table

answered Aug 19, 2021 at 11:04

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Collectives™ on Stack Overflow

How to use HASHBYTES function in SQL Server for multiple columns

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related