SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Use HASHBYTES to identify changes

By Jim Youmans,

If you need to check a row for changes you can use HASHBYTES to do so.  

Here is how to create a HASH value for the entire row.  You want to use SHA1 to make sure there are (virtually) no collisions.

For this case the hash column should be VARBINARY(20)

Make sure <primary key> is the key for that table. If there are multiple rows with that key, the hash will include them all.

The perfect use for this would be a nightly data load where you get the entire universe of data each night, maybe thousands of rows, but only a small percentage of the rows actually change.  This allows you to identify the deltas and just process them instead of having to process everything.

You could also use this to identify all row changes for a given table by comparing a saved hash value to the current hash value.  Just remember, the hash is static so if you need to update it you will have to create a process to do so.

Total article views: 1962 | Views in the last 30 days: 4
Related Articles

Seeking advice for db change mgmt process

I have to implement a new db change mgmt process and I'm seeking input/advice on tool set and proces...


Comments on Proposed Changes to the NomCom Process

On Friday Thomas LaRock posted Changes to the NomCom Process on the PASS blog for comment prior to t...


Internals Changes

Has anyone seen any blog entries or white papers about query processing internals changes


In-Memory OLTP Table Checkpoint Processes Performance Comparison

Checkpoint processes and other internal processes have undergone major changes for In Memory OLTP ta...