Microsoft® SQL Server® 2012 Bible

963

Chapter 41: Data Change Tracking and Capture

41

WHERE Name = 'Test Two';

-- Querying Net Changes - 'all with mask' option

SELECT

Operation.Description as 'Operation',

DepartmentID AS DeptID, GroupName,

sys.fn_cdc_is_bit_set

(sys.fn_cdc_get_column_ordinal

('HumanResources_Department',

'GroupName') ,

__$update_mask

)

as GroupNameUpdated,

sys.fn_cdc_has_column_changed

('HumanResources_Department', -- wrong in BOL

'GroupName',

__$update_mask)

as GroupNameHasChanged

FROM cdc.fn_cdc_get_net_changes_HumanResources_Department -- net

changes

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

'20120307 8:40pm'), -- change datetime to pick up update as

net change

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

'20121231'),

'all with mask') 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:

Operation DeptID GroupName GroupNameUpdated GroupNameHasChanged

Insert 17 SQL Rocks 0 NULL

Insert 19 PBM Rocks 0 NULL

Insert 18 Updated 2 0 NULL

1

Walking Through the Change Tables

For most ETL and synchronization operations, selecting the data as a set is the best prac-

tice, but CDC also supports walking through the change table data iteratively. Think of

these functions as CDC cursors.

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

http://www.it-ebooks.info