sys.fn_net_changes_<capture_instance> (Transact-SQL)

Applies to: SQL Server

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. For more information about the stored procedure used to create sys.fn_net_changes_<capture_instance>, see sys.sp_cdc_generate_wrapper_function (Transact-SQL).

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

If the value of either @start_time or @end_time parameter is beyond the time of lowest LSN or highest LSN, then execution of generated wrapper functions will return in error 313: Msg 313, Level 16, State 3, Line 1 An insufficient number of arguments were supplied for the procedure or function. This error should be handled by the developer.

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.

If the value of either @start_time or @end_time parameter is beyond the time of lowest LSN or highest LSN, then execution of generated wrapper functions will return in error 313: Msg 313, Level 16, State 3, Line 1 An insufficient number of arguments were supplied for the procedure or function. This error should be handled by the developer.

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

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

Table Returned

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 Template Explorer.

The wrapper functions sys.fn_all_changes_<capture_instance> and sys.fn_net_changes_<capture_instance> are dependent on the system functions cdc.fn_cdc_get_all_changes_<capture_instance> and cdc.fn_cdc_get_net_changes_<capture_instance>. Error 313 is expected if LSN range supplied is not appropriate when calling cdc.fn_cdc_get_all_changes_<capture_instance> or cdc.fn_cdc_get_net_changes_<capture_instance>. If the lsn_value parameter is beyond the time of lowest LSN or highest LSN, then execution of these functions will return in error 313: Msg 313, Level 16, State 3, Line 1 An insufficient number of arguments were supplied for the procedure or function. This error should be handled by the developer. Sample T-SQL for a workaround can be found at ReplTalk on GitHub.

See Also

• sys.fn_all_changes_<capture_instance>
• sys.sp_cdc_generate_wrapper_function (Transact-SQL)
• cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL)