cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL)

Returns one row for each change applied to the source table within the specified log sequence number (LSN) range. If a source row had multiple changes during the interval, each change is represented in the returned result set. In addition to returning the change data, four metadata columns provide the information you need to apply the changes to another data source. Row filtering options govern the content of the metadata columns as well as the rows returned in the result set. When the 'all' row filter option is specified, each change has exactly one row to identify the change. When the 'all update old' option is specified, update operations are represented as two rows: one containing the values of the captured columns before the update and another containing the values of the captured columns after the update.

This enumeration function is created at the time that a source table is enabled for change data capture. The function name is derived and uses the format **cdc.fn_cdc_get_all_changes_**capture_instance where capture_instance is the value specified for the capture instance when the source table is enabled for change data capture.

Transact-SQL Syntax Conventions

Syntax

cdc.fn_cdc_get_all_changes_capture_instance ( from_lsn , to_lsn , '<row_filter_option>' )

<row_filter_option> ::=
{ all
 | all update old
}

Arguments

• from_lsn
  The LSN value that represents the low endpoint of the LSN range to include in the result set. from_lsn is binary(10).

  Only rows in the cdc.[capture_instance]_CT change table with a value in __$start_lsn greater than or equal to from_lsn are included in the result set.

• to_lsn
  The LSN value that represents the high endpoint of the LSN range to include in the result set. to_lsn is binary(10).

  Only rows in the cdc.[capture_instance]_CT change table with a value in __$start_lsn less than or equal to from_lsn or equal to to_lsn are included in the result set.

• <row_filter_option> ::= { all | all update old }
  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 all changes within the specified LSN range. For changes due to an update operation, this option only returns the row containing the new values after the update is applied.

  • all update old
    Returns all changes within the specified LSN range. For changes due to an update operation, this option returns both the row containing the column values before the update and the row containing the column values after the update.

Table Returned

Permissions

Requires membership in the sysadmin fixed server role or db_owner fixed database role. For all other users, requires SELECT permission on all captured columns in the source table and, if a gating role for the capture instance was defined, membership in that database role. When the caller does not have permission to view the source data, the function returns error 229 ("The SELECT permission was denied on the object 'fn_cdc_get_all_changes_...', database '<DatabaseName>', schema 'cdc'.").

Remarks

If the specified LSN range does not fall within the change tracking timeline for the capture instance, the function returns error 208 ("An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes.").

Columns of data type image, text, and ntext are always assigned a NULL value when __$operation = 1 or __$operation = 3. Columns of data type varbinary(max), varchar(max), or nvarchar(max) are assigned a NULL value when __$operation = 3 unless the column changed during the update. When __$operation = 1, these columns are assigned their value at the time of the delete. Computed columns that are included in a capture instance always have a value of NULL.

Examples

Several SQL Server Management Studio templates are available that show how to use the change data capture query functions. These templates are available on the View menu in Management Studio. For more information, see Template Explorer.

This example shows the Enumerate All Changes for Valid Range Template. It uses the function cdc.fn_cdc_get_all_changes_HR_Department to report all the currently available changes for the capture instance HR_Department, which is defined for the source table HumanResources.Department in the AdventureWorks2012  database. 

-- ==================================================
-- Enumerate All Changes for Valid Range Template
-- ==================================================
USE AdventureWorks2012;
GO

DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn =
   sys.fn_cdc_get_min_lsn('HR_Department')
SET @to_lsn   = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_HR_Department
  (@from_lsn, @to_lsn, N'all');
GO

See Also

Reference

cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL)

sys.fn_cdc_map_time_to_lsn (Transact-SQL)

sys.sp_cdc_get_ddl_history (Transact-SQL)

sys.sp_cdc_get_captured_columns (Transact-SQL)

sys.sp_cdc_help_change_data_capture (Transact-SQL)

Concepts

About Change Data Capture (SQL Server)