Microsoft® SQL Server® 2012 Bible

961

Chapter 41: Data Change Tracking and Capture

41

as StartLSN, *

FROM cdc.fn_cdc_get_all_changes_HumanResources_Department

(sys.fn_cdc_map_time_to_lsn

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

sys.fn_cdc_map_time_to_lsn

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

'all') as CDC

ORDER BY __$start_lsn

The __$Operation column returned by the CDC custom table functions identifi es the type

of DML that caused the data change. Similar to a DML trigger, the data can be the before

(deleted table) or after (inserted table) image of an update.

The default 'all' parameter directs CDC to return only the after, or new, image from

an update operation. The 'all update old' option, shown in the following exam-

ple, tells CDC to return a row for both the before update image and the after update

image.

This query uses a row constructor subquery to spell out the meaning of the operation:

SELECT

sys.fn_cdc_map_lsn_to_time(__$start_lsn) as StartLSN,

Operation.Description as 'Operation',

DepartmentID, Name, GroupName

FROM cdc.fn_cdc_get_all_changes_HumanResources_Department

(sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',

'20120101'),

sys.fn_cdc_map_time_to_lsn('largest less than or equal',

'20121231'),

'all update old') as CDC

JOIN

(VALUES

(1, 'delete'),

(2, 'insert'),

(3, 'update/deleted'), -- 'all update old' option to view

(4, 'update/inserted')

) as Operation(OperationID, Description)

ON CDC.__$operation = Operation.OperationID

ORDER BY __$start_lsn

Result:

StartLSN Operation DepartmentID Name GroupName

2012-06-04 23:06:35.753 insert 17 CDC New Row SQL Rocks

2012-06-04 23:06:35.753 insert 18 Test Two CDC Rocks

2012-06-04 23:06:47.580 update/deleted 17 CDC New Row SQL Rocks

2012-06-04 23:06:47.580 update/inserted 17 Changed Name SQL Rocks

2012-06-04 23:06:51.350 insert 19 Row Three PBM Rocks

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

http://www.it-ebooks.info