cdc.<capture_instance>_CT (Transact-SQL)

Article
05/05/2023

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

The change table created when change data capture is enabled on a source table. The table returns one row for each insert and delete operation performed against the source table, and two rows for each update operation performed against the source table. When the name of the change table isn't specified at the time the source table is enabled, the name is derived. The format of the name is cdc.capture_instance_CT where capture_instance is the schema name of the source table and the source table name in the format schema_table. For example, if the table Person.Address in the AdventureWorks sample database is enabled for change data capture, the derived change table name would be cdc.Person_Address_CT.

We recommend that you do not query the system tables directly. Instead, execute the cdc.fn_cdc_get_all_changes_<capture_instance> and cdc.fn_cdc_get_net_changes_<capture_instance> functions.

Column name	Data type	Description
__$start_lsn	binary(10)	Log sequence number (LSN) associated with the commit transaction for the change. All changes committed in the same transaction share the same commit LSN. For example, if a delete operation on the source table removes two rows, the change table contains two rows, each with the same __$start_lsn value.
__$end_lsn	binary(10)	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. In SQL Server 2012 (11.x), this column is always NULL.
__$seqval	binary(10)	Sequence of the operation as represented in the transaction log. Should not be used for ordering. Instead, use the __$command_id column.
__$operation	int	Identifies the data manipulation language (DML) operation associated with the change. Can be one of the following: 1 = delete 2 = insert 3 = update (old values) Column data has row values before executing the update statement. 4 = update (new values) Column data has row values after executing the update statement.
__$update_mask	varbinary(128)	A bit mask based upon the column ordinals of the change table identifying those columns that changed.
<captured source table columns>	varies	The remaining columns in the change table are the columns from the source table that were identified as captured columns when the capture instance was created. If no columns were specified in the captured column list, all columns in the source table are included in this table.
__$command_id	int	Tracks the order of operations within a transaction.

Remarks

The __$command_id column was introduced in a cumulative update in versions 2012 through 2016. For version and download information, see KB article 3030352 at FIX: The change table is ordered incorrectly for updated rows after you enable change data capture for a Microsoft SQL Server database. For more information, see CDC functionality may break after upgrading to the latest CU for SQL Server 2012, 2014 and 2016.

Captured Column Data Types

Captured columns included in this table have the same data type and value as their corresponding source columns with the following exceptions:

Timestamp columns are defined as binary(8).
Identity columns are defined as either int or bigint.

However, the values in these columns are the same as the source column values.

Large Object Data Types

Columns of data type image, text, and ntext are always assigned a NULL value when __$operation = 1 or __$operation = 3. Columns of data type varbinary(max), varchar(max), or nvarchar(max) are assigned a NULL value when __$operation = 3 unless the column changed during the update. When __$operation = 1, these columns are assigned their value at the time of the delete. Computed columns that are included in a capture instance always have a value of NULL.

By default, the maximum size that can be added to a captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement is 65,536 bytes or 64 KB. To increase this size to support larger LOB data, use the Configure the max text repl size Server Configuration Option to specify a larger maximum size. For more information, see Configure the max text repl size Server Configuration Option.

Data Definition Language Modifications

DDL modifications to the source table, such as adding or dropping columns, are recorded in the cdc.ddl_history table. These changes aren't applied to the change table. That is, the definition of the change table remains constant. When inserting rows into the change table, the capture process ignores those columns that don't appear in the captured column list associated with the source table. If a column appears in the captured column list that is no longer in the source table, the column is assigned a null value.

Changing the data type of a column in the source table is also recorded in the cdc.ddl_history table. However, this change does alter the definition of the change table. The data type of the captured column in the change table is modified when the capture process encounters the log record for the DDL change made to the source table.

If you need to modify the data type of a captured column in the source table in a way that decreases the size of the data type, use the following procedure to ensure that the equivalent column in the change table can be successfully modified.

In the source table, update the values in the column to be modified to fit into the planned data type size. For example, if you change the data type from int to smallint, update the values to a size that fits in the smallint range, -32,768 to 32,767.
In the change table, perform the same update operation to the equivalent column.
Alter the source table by specifying the new data type. The data type change is propagated successfully to the change table.

Data Manipulation Language Modifications

When insert, update, and delete operations are performed on a change data capture enabled source table, a record of those DML operations appears in the database transaction log. The change data capture process retrieves information about those changes from the transaction log, and adds either one or two rows to the change table to record the change. Entries are added to the change table in the same order that they were committed to the source table. That said, the commit of change table entries must typically be performed on a group of changes rather than performed per each entry.

An insert operation results in one row added to the change table; a delete operation results in one row added to the change table; if SQL Server implements an update as a "deferred update", which means as a pair of delete and insert operations, the update operation results in two rows added to the change table: the first row reflecting the deletion of the captured data, and the second row reflecting the insertion of the updated, captured data; if SQL Server doesn't implement an update as a "deferred update", the update operation results in two rows added to the change table: the first row reflecting the captured data before the update, and the second row reflecting the captured data after the update.

Within the change table entry, the __$start_lsn column is used to record the commit LSN that is associated with the change to the source table, the __$command_id column is used to order the change within its transaction, and the __$operation column is used to record the operation performed. Together, these metadata columns can be used to make sure that the commit order of the source changes is preserved. Because the capture process obtains its change information from the transaction log, it's important to note that change table entries don't appear synchronously with their corresponding source table changes. Instead, corresponding changes appear asynchronously, after the capture process has processed the relevant change entries from the transaction log.

For insert and delete operations, all the bits in the update mask are set. For update operations, the update mask in both the update old and update new rows will be modified to reflect the columns that changed during update.