So we've started exploring using change data capture on one of our production databases. We'd like to know the datetime of each change. Reading through walkthrough's and tutorials etc it seems that the standard approach is to use the LSN to relate to the
cdc.lsn_time_mapping system table. This approach works but is not very straightforward nor performant when talking about 100's of thousands of changes a day.
In a test environment I made the following adjustment to the change track tables. I issued an
ALTER TABLE statement to add a column to the end called
[__ChangeDateTime] and made it's default value
GetDate(). The approach seems to work, the change tracking still functions normally, the datetime's are being captured. But mucking around with system tables makes me a little nervous.
If this isn't a system field that Microsoft added from the beginning they must of had their reasons. Since they instead opted for the LSN to cdc.lsn_time_mapping approach am I setting myself up for problems by creating my own hack this way?
Discovered during testing that GetDate() at times isn't precise enough for our needs - multiple changes sharing the same time. Recommend using sysdatetime() and datetime2 to move the value out to the nanosecond. Option for 2008+ only obviously.