Sql-server – SQL Server Change Data Capture order of records

change-data-capturesql server

Does anybody know if change data capture functions return always data in the order of occurence?. In a CDC system is quite important to get the data modifications in the order they have been generated.

Looking to the cdc.fn_cdc_get_all_changes_XXX functions, there is no order by clause in any of the underlying queries.

Thanks

Best Answer

Let me quote from the manual:

Track Data Changes (SQL Server)

Change tracking is based on committed transactions. The order of the changes is based on transaction commit time. This allows for reliable results to be obtained when there are long-running and overlapping transactions. Custom solutions that use timestamp values must be specifically designed to handle these scenarios.

and cdc.fn_cdc_get_all_changes_ (Transact-SQL) says:

Commit LSN associated with the change that preserves the commit order of the change. Changes committed in the same transaction share the same commit LSN value.

for one of the returned columns (_$start_lsn) in get_all_changes.

Related Question