sys.fn_net_changes_<capture_instance> (Transact-SQL)

Wrappers for the net changes query functions. The scripts that are required to create these functions are generated by the sys.sp_cdc_generate_wrapper_function stored procedure.

Topic link iconTransact-SQL Syntax Conventions

Syntax

fn_net_changes_<capture_instance> ('start_time','end_time','<row_filter_option>' )

<capture_instance> ::= The name of the capture instance.
<row_filter_option> ::=
{ all
  | all with mask
  | all with merge
}

Arguments

  • 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.

Table Returned

Column name

Column type

Description

<columns from @column_list>

varies

The columns that are identified in the column_list argument to the sp_cdc_generate_wrapper_function when it is called to generate the script to create the wrapper. If column_list is NULL, all tracked source columns will appear in the result set.

__CDC_OPERATION

nvarchar(2)

An operation code that indicates which operation is required to apply the row to the target environment. The operation will vary based on the value of the argument row_filter_option that is supplied in the following call:

row_filter_option = 'all' , 'all with mask'

'D' - delete operation

'I' - insert operation

'UN' - update operation

row_filter_option = 'all with merge'

'D' - delete operation

'M' - either insert operation or update operation

<columns from @update_flag_list>

bit

A bit flag that is named by appending _uflag to the column name. The flag takes on a nonnull value only when row_filter_option = 'all with mask' and __CDC_OPERATION = 'UN'. It is set to 1 if the corresponding column was modified within the query window. Otherwise, 0.

Remarks

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.