After recording data changes, the data needs to be accessible in an efficient and orderly manner.
The ordered access comes through the use of the indexes that are provided on the change tracking table and on the CDC change tables.
There is a relationship between the records in the change tracking table and the change tables,each change tracking table record has zero, one, or two associated change table records depending on the Level of the CDC policy.
The connection between these two tables is important. The type of change activity (operation) is in the Change Tracking Table, and the data resulting from the operation is in the change table. Each time the record is modified, there is a record added to the Change Tracking Table based on the operation. Operations that are tracked, include: create, update, and delete. See
Change Tracking Table for a complete list of operations.
The operations are ordered based on change sequence order field. The change sequence field has an increasing value as records are added to the table.
When extracting the change data, it often needs to read the data in the order it occurred, either by the sequence of entry, or by time of entry. Indexes ease this process.
A CDC policy designer selects the fields and component order for this index when defining a policy. A final component is always added after the identifying fields , the _Change-Sequence field to preserve the order.
The _Change-Sequence-Id index establishes a (foreign key) relationship to the Change Tracking Table.