sys.fn_net_changes_<capture_instance> (Transact-SQL)

start_time

The datetime value that represents the low endpoint of the range of change table entries to include in the result set.

Only rows in the cdc.<capture_instance>_CT change table that have an associated commit time strictly greater than start_time are included in the result set.

If a value of NULL is supplied for this argument, the low endpoint of the query range will correspond to the low endpoint of the valid range for the capture instance.

end_time

The datetime value that represents the high endpoint of the range of change table entries to include in the result set.

This parameter can take on one of two meanings, depending on the value chosen for @closed_high_end_point when sys.sp_cdc_generate_wrapper_function is called to generate the script to create the wrapper function:

• @closed_high_end_point = 1

  Only rows in the cdc.<capture_instance>_CT change table that have a value in __$start_lsn and a corresponding commit time less than or equal to start_time are included in the result set.

• @closed_high_end_point = 0

  Only rows in the cdc.<capture_instance>_CT change table that have a value in __$start_lsn and a corresponding commit time strictly less than start_time are included in the result set.

If a value of NULL is supplied for this argument, the high endpoint of the query range will correspond to the high endpoint of the valid range for the capture instance.

<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 final content of a changed row in the content columns, and the operation that is required to apply the row in the metadata column __CDC_OPERATION.

all with mask

Returns final content of all changed rows in the content columns, and the operation that is necessary to apply each row in the metadata column __CDC_OPERATION. If an update flag list was specified when you generated the script to create the wrapper function, this option is required to populate the update mask.

all with merge

Returns final content of all changed rows in the content columns.

The column __CDC_OPERATION will be one of the following two values:

• D, if the row must be deletedr

• M, if the row must be inserted or updated.

The logic to determine whether an insert or update is needed to apply a change to the target adds to query complexity. Use this option for improved performance when it is not necessary to differentiate between insert and update operations. This approach works best in target environments where a merge operation is available directly, such as a SQL Server 2008 environment.

The fn_net_changes_<capture_instance> function serves as a wrapper for the cdc.fn_cdc_get_net_changes_<capture_instance> query function. The sys.sp_cdc_generate_wrapper stored procedure is used to create the script for the wrapper.

Wrapper functions are not created automatically. There are two things you must do to create wrapper functions:

1. Run the stored procedure to generate the script to create the wrapper.

2. Execute the script to actually create the wrapper function.

Wrapper functions enable users to systematically query for changes that occurred within an interval bounded by datetime values instead of by LSN values. The wrapper functions perform all the required conversions between the provided datetime values and the LSN values needed internally as arguments to the query functions. When the wrapper functions are used serially to process a stream of change data, they ensure that no data is lost or repeated provided that the following convention is followed: the @end_time value of the interval associated with one call is supplied as the @start_time value for the interval associated with the subsequent call.

By using the @closed_high_end_point parameter when you create the script, you can generate wrappers to support either a closed upper bound or an open upper bound on the specified query window. That is, you can decide whether entries that have a commit time equal to the upper bound of the extraction interval are to be included in the interval. By default, the upper bound is included.

The result set that is returned by the net changes wrapper function returns only those tracked columns that were in the @column_list when the wrapper was generated. If @column_list is NULL, all tracked source columns are returned. The source columns are followed by an operation column, __CDC_OPERATION, which is a one- or two-character column that identifies the operation.

Bit flags are then appended to the result set for each column that is identified in the parameter @update_flag_list. For the net changes wrapper, the bit flags will always be NULL if the @row_filter_option that is used in the call to the wrapper function is 'all' or 'all with merge'. If the @row_filter_option is set to 'all with mask', and __CDC_OPERATION is 'D' or 'I', the value of the flag will also be NULL. If __CDC_OPERATION is 'UN', the flag will be set to 1 or 0, depending on whether the net update operation caused a change to the column.

The change data capture configuration template 'Instantiate CDC Wrapper TVFs for Schema' shows how to use the sp_cdc_generate_wrapper_function stored procedure to obtain CREATE scripts for all of the wrapper functions for a schema’s defined query functions. The template then creates those scripts. For more information about templates, see Using SQL Server Management Studio Templates.

Reference