3

I'm working on a custom application that needs to determine changes to a particular column, on particular rows in our database (MS SQL 2008R2).

When the row is created a created & modify timestamp are inserted. Anytime any of the columns change, the modified timestamp is updated.

I' need to be able to fetch all the records where one particular column was changed between the time my application last ran, and now. Further more, i really only care if this one particular column value changed from a "N" to a "Y".

I want to do all this, without altering the DB schema, or using triggers due to their overhead.

My research led me to Change Data Capture, would this be a good use case to use Change Data Capture?

Is it possible to limit the overhead on CDC to an item only changing to a specific value, my table has approx ~200k rows, with 300mb in index and 200mb in data, and rows are heavily modified, so i'm concerned about the amount of data that will be captured.

The only alternate approach that i am aware of, is to capture the parent event (row created) in a separate audit table, and then compare it to the original table each time the application runs to surmise the change, is there an advantage to doing it this, or is there any simpler way to do it?

  • Depending on what you are trying to achieve overall, there might be lighter weight solutions.What will consume the modified timestamp? - e.g. used to determine changes to process by some kind of job? timestamp shown in the user interface? – Paul Holmes Sep 25 '17 at 11:38
  • It's a console application that calls some webhooks to send some data to 3rd party APIs. The application runs every 15 minutes. Currently i grab all the records with create timestamps between last run and now, and insert records into my own audit table. Then i check all the rows modified, if the row exists in my audit table, i determine if the column i cared about changed and then fire off the API and remove from my audit table. Works, but not very graceful. – DDulla Sep 25 '17 at 15:02
1

Change Tracking would be a lighter weight solution to your needs, and more robust than using a time stamp (even if driven from CDC in the way you describe).

You can enable tracking at the column level. The versioning number - used to determine what has changed since your last pass - is safe from race conditions. Crucially, it is issued on transaction completion. Homebrew solutions can end up using a value (e.g. timestamp) issued at another point in time, and not of sufficient precision to always determine one transaction from another. This leads to edge cases where the determination of change may not be correct.

The following series of MS documents describe how to set up change tracking.

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server

(Although lightweight, it is still important to test performance using a volume of change representative of your production environment. There are parameters such as change retention period that can be adjusted to balance performance.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.