Export (0) Print
Expand All

sys.fn_cdc_increment_lsn (Transact-SQL)

Returns the next log sequence number (LSN) in the sequence based upon the specified LSN.

Topic link iconTransact-SQL Syntax Conventions


sys.fn_cdc_increment_lsn (lsn_value)

lsn_value

LSN value. lsn_value is binary(10).

binary(10)

The LSN value returned by the function is always greater than the specified value, and no LSN values exist between the two values.

To systematically query a stream of change data over time, you can repeat the query function call periodically, each time specifying a new query interval to bound the changes returned in the query. To help insure that no data is lost, the upper bound for the previous query is often used to generate the lower bound for the subsequent query. Because the query interval is a closed interval, the new lower bound must be larger than the previous upper bound, but small enough to ensure no changes have LSN values that lie between this value and the old upper bound. The function sys.fn_cdc_increment_lsn is used to obtain this value.

Requires membership in the public database role.

The following example uses sys.fn_cdc_increment_lsn to generate a new lower bound value for a change data capture query based on the upper bound saved from a previous query and saved in the variable @save_to_lsn.

USE AdventureWorks;
GO
DECLARE @from_lsn binary(10), @to_lsn binary(10), @save_to_lsn binary(10);
SET @save_to_lsn = <previous_upper_bound_value>;
SET @from_lsn = sys.fn_cdc_increment_lsn(@save_to_lsn);
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT * from cdc.fn_cdc_get_all_changes_HumanResources_Employee( @from_lsn, @to_lsn, 'all' );
GO
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft