CODEWITHSUNDEEP
sql-server
Vikas J
Vikas J

Reputation: 887

How to use HASHBYTES function in SQL Server for multiple columns

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?

Upvotes: 14

Views: 50600

Answers (4)

Nick.Mc
Nick.Mc

Reputation: 19204

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

Upvotes: 2

Oleg
Oleg

Reputation: 1765

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

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

Upvotes: 8

Don Pedro Alvares
Don Pedro Alvares

Reputation: 111

Use this:

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

Upvotes: 11

Brian
Brian

Reputation: 7299

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:''.

Upvotes: 11

Related Questions