sys.fn_all_changes_<capture_instance> (Transact-SQL)

Artikel
02/28/2023

Applies to: SQL Server

Wrappers for the all 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_all_changes_<capture_instance>, see sys.sp_cdc_generate_wrapper_function (Transact-SQL).

Transact-SQL syntax conventions

Syntax

fn_all_changes_<capture_instance> ('start_time' ,'end_time', '<row_filter_option>' )  
  
<capture_instance> ::= The name of the capture instance.  
<row_filter_option> ::=  
{ all  
  | all update old  
}

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 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 possible meanings depending on the value chosen for @closed_high_end_point when sys.sp_cdc_generate_wrapper_function is called to generate the create script for the wrapper function:

@closed_high_end_point = 1

Only rows in the cdc.<capture_instance>_CT change table that have an associated commit time less than or equal to end_time are included in the result set.
@closed_high_end_point = 0

Only rows in the cdc.capture_instance_CT change table that have an associated commit time strictly less than end_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 update old }

An option that governs the content of the metadata columns and the rows that are returned in the result set.

Can be one of the following options:

all
Returns all changes within the specified LSN range. For changes that occur because of an update operation, this option only returns the row that contains the new values after the update is applied.

all update old
Returns all changes within the specified LSN range. For changes that occur because of an update operation, this option returns the two rows that contain the column values before the update and after the update.

Table Returned

Column name	Column type	Description
__CDC_STARTLSN	binary(10)	The commit LSN of the transaction that is associated with the change. All changes that are committed in the same transaction share the same commit LSN.
__CDC_SEQVAL	binary(10)	Sequence value that is used to order the row changes in a transaction.
<columns from @column_list>	varies	The columns that are identified in the column_list argument to `sp_cdc_generate_wrapper_function` when it is called to generate the script that creates the wrapper function.
__CDC_OPERATION	nvarchar(2)	Operation code that indicates the operation that is required to apply the row to the target environment. It will vary based on the value of the argument row_filter_option supplied in the call: row_filter_option = 'all' 'D' - delete operation 'I' - insert operation 'UN' - update operation new values row_filter_option = 'all update old' 'D' - delete operation 'I' - insert operation 'UN' - update operation new values 'UO' - update operation old values
<columns from @update_flag_list>	bit	A bit flag is named by appending _uflag to the column name. The flag is always set to NULL when __CDC_OPERATION is 'D', 'I', of 'UO'. When __CDC_OPERATION is 'UN', it is set to 1 if the update produced a change to the corresponding column. Otherwise, 0.

Remarks

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

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

Run the stored procedure to generate the script to create the wrapper.
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 all changes wrapper function returns the __$start_lsn and __$seqval columns of the change table as columns __CDC_STARTLSN and __CDC_SEQVAL, respectively. It follows these with only those tracked columns that appeared in the @column_list parameter 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 @update_flag_list parameter. For the all changes wrapper, the bit flags will always be NULL if __CDC_OPERATION is 'D', 'I', or 'UO'. If __CDC_OPERATION is 'UN', the flag will be set to 1 or 0, depending on whether the 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.

Delen via