.png)
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).
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. |
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).
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');
