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?
Best Answer
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.)