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
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.
Found it documented:
The function cdc.fn_cdc_get_all_changes_ returns all changes that occurred for the specified interval. This function is always generated. Entries are always returned sorted, first by the transaction commit LSN of the change, and then by a value that sequences the change within its transaction.
Work with Change Data (SQL Server)
Of course if you embed the query against the change function in a larger query, you have to explicitly order the results.
