I am using the SQL Change Data Capture feature to retrieve historical data from my tables.
During testing, I noticed that when I insert/update a row and query the change data right afterwards (see my earlier question on how I do that), the result will turn up empty.
I played around with it and noticed that if I wait 10 seconds between the data manipulation and the history query, the history will be up to date.
I am aware that CDC functionality is based on the transaction logs somehow and that this probably is the reason why there is a delay between data changes and the cdc history table being updated.
Now my question:
- Is this delay somewhere explained in the CDC documentation? I could not find any mention of it.
- Is there a way to manually make sure that the history table is up to date before I query it? (somehow triggering CDC to check the logs?)
- How does CDC work together with transactions? Will I for example be able to query historical data that I have overwritten in the same transaction? Or does the transaction first have to commit for the cdc data to become availalbe?