can we use checksum to check that row has been changed(sql server)?

Question

Create table Info
(
Personid int,
PersonNo int
)

insert into Info(Personid,PersonNo) values(3,4)

Here we can see the checksum value of the particular row

Select CHECKSUM(Personid ,PersonNo ) from Info

Now i update the value of the PersonNo and check the checksum of the particular row

CheckSum value has been changed

so on this behalf i can know that particular row has been changed

so my question is that Is this safe or accurate?

Answer 1

It is not safe, CHECKSUM could produce duplicates for different data.

In SQL Server, to check whether the row was changed in between, ROWVERSION (or it's synonim TIMESTAMP) is usually used, but you have to add it as a column to a table. This is not really a timestamp but rather simply a database-wide counter and is maintained automatically for every updated row. Note that it will change when an UPDATE statement is issued, the content actually may stay the same, as opposed to checksum.

Or you can roll your own real timestamp - add a datetime column of required precision and update it manually.

Answer 2

From CHECKSUM (Transact-SQL)

CHECKSUM satisfies the properties of a hash function: CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator. For this definition, null values of a specified type are considered to compare as equal. If one of the values in the expression list changes, the checksum of the list also generally changes.

Do note though that

However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using HashBytes instead. When an MD5 hash algorithm is specified, the probability of HashBytes returning the same result for two different inputs is much lower than that of CHECKSUM.