Microsoft® SQL Server® 2012 Bible

959

Chapter 41: Data Change Tracking and Capture

41

The following sample query defi nes a range beginning with Jan 20 and ending with Jan 24,

and returns the LSNs that bound that range:

select

sys.fn_cdc_map_time_to_lsn

('smallest greater than or equal', '20120101')

as BeginLSN,

sys.fn_cdc_map_time_to_lsn

('largest less than or equal', '20121231')

as EndLSN;

Result:

BeginLSN EndLSN

---------------------- ----------------------

0x0000002F000001330040 0x0000003B000002290001

The sys.fn_cdc_get_min_lsn() and sys.fn_cdc_get_max_lsn() functions serve

as anchor points to begin the walk through the log. The min function requires a table and

returns the oldest log entry. The max function has no parameters and returns the most

recent LSN in the change tables:

DECLARE

@BeginLSN VARBINARY(10) = 

sys.fn_cdc_get_min_lsn('HumanResources_Department');

SELECT @BeginLSN;

DECLARE

@EndLSN VARBINARY(10) = 

sys.fn_cdc_get_max_lsn();

SELECT @EndLSN;

There’s not much benefi t to knowing the hexadecimal LSN values by themselves, but the

LSNs can be passed to other functions to select data from the change tables.

Querying the Change Tables

Change tracking creates a function for each table being tracked using the name cdc

.fn_cdc_get_all_changes concatenated with the schema and name of the table. The

following script uses the sys.fn_cdc_map_time_to_lsn function to determine the LSN

range values, store them in variables, and then pass the variables to the department tables’

custom change data capture function:

-- with variables

DECLARE

@BeginLSN VARBINARY(10) = 

sys.fn_cdc_map_time_to_lsn

('smallest greater than or equal', '20120101'),

@EndLSN VARBINARY(10) = 

c41.indd 959c41.indd 959 7/31/2012 10:17:22 AM7/31/2012 10:17:22 AM

http://www.it-ebooks.info