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?