How to use HASHBYTES function in sql server for multiple columns

Transact-SQL
1

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 above using Hashbytes instead of checksum?

2

Presumably you'd concatenate all the relevant columns?:

create table dbo.chksum_demo1
(
    id int not null,
    name varchar(25),
    address varchar(250),
    HashValue as Hashbytes('SHA2_256', CAST(id AS varchar(10)) + name + address),
    CONSTRAINT PK_chksum_demo1 PRIMARY KEY (Id)
)
2 Likes
3

Thanks for the prompt reply Scott. But had 1 question you casted id from int to varchar, Does HashBytes only supports varchar ?

4

Yes, or varbinary.

5

You can also use CONCAT instead - which will simplify your code and eliminate having to cast/convert column data.

HashValue as Hashbytes('SHA2_256', CONCAT(id, name, address)),
2 Likes
6

Depending on the specific SQL version you're on. Yes, CONCAT if 2012+, but only then.

7

Sorry my bad I forgot to mention SQL version it is 2014

8

perfect thanks.

9

How does CONCAT behave with NULL values?

This is how I script hashbytes (only now learned about the CONCAT function):

Hashbytes('SHA2_256', 
   CAST(id AS varchar(10)) + '|' +
   COALESCE(name, '') + '|' + 
   COALESCE(address, '')
   ),

If you don't place separators between the values of the columns, the hash of the values
1, 11 and 121
will be the same as the hash for
11, 1 and 121 and
11, 12, 1 and
...
concatenated without separators, in every case the string 111121 is passed to the hashbytes function.

With -separators,the hash function gets different strings to hash:
1|11|121
11|1|121
11|12|1
resulting in different hashes.

1 Like
10

CONCAT converts nulls to an empty string