Microsoft® SQL Server® 2012 Bible

962

Part VII: Monitoring and Auditing

2012-06-04 23:06:51.350 insert 20 Row Four TVP Rocks

2012-06-04 23:06:54.570 update/deleted 18 Test Two CDC Rocks

2012-06-04 23:06:54.570 update/inserted 18 Test Two T-SQL Rocks

2012-06-04 23:06:59.530 delete 20 Row Four TVP Rocks

Querying Net Changes

All the previous queries returned all the changes within the requested time frame. But for

many ETL operations or synchronizations, only the fi nal net values are needed. Change data

capture can automatically determine the net, or fi nal, values. Use the cdc.fn_cdc_get_

net_changes_schema_table function to return the net changes:

-- Querying Net Changes - 'all' option

SELECT

sys.fn_cdc_map_lsn_to_time(___$start_lsn) as StartLSN,

Operation.Description as 'Operation',

DepartmentID, Name, GroupName

FROM cdc.fn_cdc_get_net_changes_HumanResources_Department

-- net changes

(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

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:47.580 insert 17 Changed Name SQL Rocks

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

2012-06-04 23:06:54.570 insert 18 Test Two T-SQL Rocks

When querying net changes using Change Data Capture, it’s also possible to work with a

column mask to determine whether a given column has changed. In the following query,

the all with mask option and sys.fn_cdc_has_column_changed function are used

together to test for changes in the GroupName column:

-- update the GroupName column

UPDATE HumanResources.Department

SET GroupName = 'Updated 2'

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

http://www.it-ebooks.info