Share via

  • Article

cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL)

Returns one net change row for each source row changed within the specified LSN range. That is, when a source row has multiple changes during the LSN range, a single row that reflects the final content of the row is returned by the function. For example, if a transaction inserts a row in the source table and a subsequent transaction within the LSN range updates one or more columns in that row, the function returns only one row, which includes the updated column values.

This enumeration function is created when a source table is enabled for change data capture and net tracking is specified. To enable net tracking, the source table must have a primary key or unique index. The function name is derived and uses the format cdc.fn_cdc_get_net_changes_capture_instance, where capture_instance is the value specified for the capture instance when the source table was enabled for change data capture. For more information, see sys.sp_cdc_enable_table (Transact-SQL).

Topic link iconTransact-SQL Syntax Conventions

Syntax

cdc.fn_cdc_get_net_changes_capture_instance(from_lsn,to_lsn,'<row_filter_option>')

<row_filter_option> ::=
{ all
 | all with mask
 | all with merge
}

Arguments

  • from_lsn
    The LSN that represents the low endpoint of the LSN range to include in the result set. from_lsn is binary(10).

    Only rows in the cdc.[capture_instance]_CT change table with a value in __$start_lsn greater than or equal to from_lsn are included in the result set.

  • to_lsn
    The LSN that represents the high endpoint of the LSN range to include in the result set. to_lsn is binary(10).

    Only rows in the cdc.[capture_instance]_CT change table with a value in __$start_lsn less than or equal to from_lsn or equal to to_lsn are included in the result set.

  • <row_filter_option> ::= { all | all with mask | all with merge }
    An option that governs the content of the metadata columns as well as the rows returned in the result set. Can be one of the following options:

    • all
      Returns the LSN of the final change to the row and the operation needed to apply the row in the metadata columns __$start_lsn and __$operation. The column __$update_mask is always NULL.

    • all with mask
      Returns the LSN of the final change to the row and the operation needed to apply the row in the metadata columns __$start_lsn and __$operation. In addition, when an update operation returns (__$operation = 4) the captured columns modified in the update are marked in the value returned in __$update_mask.

    • all with merge
      Returns the LSN of the final change to the row in the metadata columns __$start_lsn. The column __$operation will be one of two values: 1 for delete and 5 to indicate that the operation needed to apply the change is either an insert or an update. The column __$update_mask is always NULL.

      Because the logic to determine the precise operation for a given change adds to query complexity, this option is designed to improve query performance when it is sufficient to indicate that the operation needed to apply the change data is either an insert or an update, but it is not necessary to explicitly distinguish between the two. This option is most attractive in target environments where a merge operation is available directly, such as a SQL Server 2008 environment.

Table Returned

Column name

Data type

Description

__$start_lsn

binary(10)

LSN associated with the commit transaction for the change.

All changes committed in the same transaction share the same commit LSN. For example, if an update operation on the source table modifies two columns in two rows, the change table will contain four rows, each with the same __$start_lsn value.

__$seqval

binary(10)

Sequence value used to order the row changes within a transaction.

__$operation

int

Identifies the data manipulation language (DML) operation needed to apply the row of change data to the target data source.

If the value of the row_filter_option parameter is all or all with mask, the value in this column can be one of the following values:

1 = delete

2 = insert

4 = update

If the value of the row_filter_option parameter is all with merge, the value in this column can be one of the following values:

1 = delete

5 = either insert or update

A value of 5 indicates that it is not known whether the row is already present and only has to be updated, or whether the row is not currently present and must be inserted.

__$update_mask

varbinary(128)

A bit mask with a bit corresponding to each captured column identified for the capture instance. This value has all defined bits set to 1 when __$operation = 1 or 2. When __$operation = 3 or 4, only those bits corresponding to columns that changed are set to 1.

<captured source table columns>

varies

The remaining columns returned by the function 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 returned.

Permissions

Requires membership in the sysadmin fixed server role or db_owner fixed database role. For all other users, requires SELECT permission on all captured columns in the source table and, if a gating role for the capture instance was defined, membership in that database role. When the caller does not have permission to view the source data, the function returns error 208 (Invalid object name).

Remarks

If the specified LSN range does not fall within the change tracking timeline for the capture instance, the function returns error 208 (Invalid object name).

Examples

The following example uses the function cdc.fn_cdc_get_net_changes_HR_Department to report the net changes made to the source table HumanResources.Department during a specific time interval.

First, the GETDATE function is used to mark the beginning of the time interval. After several DML statements are applied to the source table, the GETDATE function is called again to identify the end of the time interval. The function sys.fn_cdc_map_time_to_lsn is then used to map the time interval to a change data capture query range bounded by LSN values. Finally, the function cdc.fn_cdc_get_net_changes_HR_Department is queried to obtain the net changes to the source table for the time interval. Notice that the row that is inserted and then deleted does not appear in the result set returned by the function. This is because a row that is first added and then deleted within a query window produces no net change on the source table for the interval. Before you run this example, you must first run example B in sys.sp_cdc_enable_table (Transact-SQL).

USE AdventureWorks;
GO
DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);
-- Obtain the beginning of the time interval.
SET @begin_time = GETDATE() -1;
-- DML statements to produce changes in the HumanResources.Department table.
INSERT INTO HumanResources.Department (Name, GroupName)
VALUES (N'MyDept', N'MyNewGroup');

UPDATE HumanResources.Department
SET GroupName = N'Resource Control'
WHERE GroupName = N'Inventory Management';

DELETE FROM HumanResources.Department
WHERE Name = N'MyDept';

-- Obtain the end of the time interval.
SET @end_time = GETDATE();
-- Map the time interval to a change data capture query range.
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

-- Return the net changes occurring within the query window.
SELECT * FROM cdc.fn_cdc_get_net_changes_HR_Department(@from_lsn, @to_lsn, 'all');