Adding Change Tracking to a SQL Table

This is a method to add an Updated Timestamp to a SQL Table where one does not exist and the source application does not provide updated information as data is changed.

This would normally be implemented within the Application or perhaps SQL Triggers, however this is another approach with Data Sync via a Hash value stored on the row.

You might want to consider this if your Tables are large and your syncing into something that is slow or on the internet. i.e. Dynamics CRM where Data Sync Incremental sync would be faster, with a Change Timestamp you can select records that have been changed since a point in time.

I have started out with a simple Table, a copy of the Suppliers Table from the Northwind database. This does not include any tracking information.

CREATE TABLE [dbo].[MySuppliers] (
    [SupplierID] int NOT NULL PRIMARY KEY CLUSTERED,
    [CompanyName] nvarchar(40) NOT NULL,
    [ContactName] nvarchar(30) NULL,
    [ContactTitle] nvarchar(30) NULL,
    [Address] nvarchar(60) NULL,
    [City] nvarchar(15) NULL,
    [Region] nvarchar(15) NULL,
    [PostalCode] nvarchar(10) NULL,
    [Country] nvarchar(15) NULL,
    [Phone] nvarchar(24) NULL,
    [Fax] nvarchar(24) NULL,
    [HomePage] nvarchar(MAX) NULL
)
GO

Next we add 2 columns, 1 to hold the hash value and 1 to hold the time of change.

ALTER TABLE [dbo].[MySuppliers] ADD [Hash] nvarchar(50) NULL
GO
ALTER TABLE [dbo].[MySuppliers] ADD [Updated] datetime DEFAULT(GETUTCDATE()) NOT NULL

Next we setup the Data Sync project, connect the Source and Target to the same SQL Table, MySuppliers in this case.

Then Create 2 Calculated Columns which will provide the change tracking information.

1- MyHash - Returns a String MD5 hash of the values in the row.

MD5HASH(CONCAT(columns))

2- MyUpdated - Returns DateTime of the Change (when this project is run)

Map these new Calculated columns to Hash and Updated in the Target.

Now for the Trick, we want to update the Time and hash values only when the data has changed to do this we set the TriggerUpdate property in the schema map = False for all columns Except MyHash.

Now the first time we run the project all rows will be updated, but on the next run only the rows where the Hash is different will the Updated value be changed.

To generate the change tracking, you would run this as a pre-step in Ouvvi to your project you want to create an Incremental Sync. The benefit being if you have a Table of say 1 Million Rows into Dynamics CRM running this against SQL locally first will ensure the subsequent Sync into CRM with Incremental mode is fast as only changed rows need be loaded.