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.
.gif "Topic link icon")
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
Column name |
Data type |
Description |
|---|---|---|
__$start_lsn |
binary(10) |
Commit LSN associated with the change that preserves the commit order of the change. Changes committed in the same transaction share the same commit LSN value. |
__$seqval |
binary(10) |
Sequence value used to order changes to a row within a transaction. |
__$operation |
int |
Identifies the data manipulation language (DML) operation needed to apply the row of change data to the target data source. Can be one of the following: 1 = delete 2 = insert 3 = update (captured column values are those before the update operation). This value applies only when the row filter option 'all update old' is specified. 4 = update (captured column values are those after the update operation) |
__$update_mask |
varbinary(128) |
A bit mask with a bit corresponding to each captured column identified for the capture instance. This value has all defined bits set to 1 when __$operation = 1 or 2. When __$operation = 3 or 4, only those bits corresponding to columns that changed are set to 1. |
<captured source table columns> |
varies |
The remaining columns returned by the function are the captured columns identified when the capture instance was created. If no columns were specified in the captured column list, all columns in the source table are 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 Using SQL Server Management Studio Templates.
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 AdventureWorks2008R2 database.
-- ==================================================
-- Enumerate All Changes for Valid Range Template
-- ==================================================
USE AdventureWorks2008R2;
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