sys.fn_all_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 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 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 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.

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:

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 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 Using SQL Server Management Studio Templates.

Reference