How to Detect Table Changes or Changes in Data

This is a handy method can be used to detect if a change has happened to any set of data.  This can include an entire table or even just two rows.

[cc lang=”sql”]
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM Sales.SalesOrderDetail
[/cc]

From our execution, we see that a number is returned.

The number itself doesn’t have a particular meaning. All it is, is a hash. A hash is a number or sequence that is generated that represents the data you are hashing. The idea is that, if you change the underlying data you are hashing, then the hash will change. In this case, we are using the hash to say, “This number represents all the data contained ithin this table.” If the data changes, then the hash will change also.

So let’s change a minute piece of data and see what happens. We’ll update the unitprice within one row by a penny.

[cc lang=”sql”]
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM Sales.SalesOrderDetail

UPDATE Sales.SalesOrderDetail
SET UnitPrice = UnitPrice + .01
WHERE SalesOrderID = 43659

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM Sales.SalesOrderDetail
[/cc]
Here’s what we see:

So what use is this for? Well, there are plenty of uses. The most common use comes when trying to persist data. What this means, is that say we have lookup tables that we want to cache either on the web tier, or even on the client’s machines. What we can do, is send the lookup tables, along with a hash key that represents the state of the data. Then, we can have the web server call the database, say every hour, to see if the hash has changed. If it has, then we will send a new copy of the data to the webserver, which can then in turn submit the data to the client machine (if we are storing it there). Sure beats having to submit the data over and over for every call. And it is also faster for the end-user to have the data persisted somewhere other than the database server.