Monday, January 7, 2013

Enable Change Data Capture on Table

Microsoft SQL Server 2008 has introduces new feature for data change tracking on tables called CDC (Change Data Capture). below are steps to enable change tracking on specific table "dbo.Users"

1) First enable CDC on database using below syntax:

    EXEC sys.sp_cdc_disable_db

it will create two jobs
- cdc.DatabaseName_capture
- cdc.DatabaseName_cleanup

2) Enable CDC on table "dbo.Users"

    IF NOT EXISTS (SELECT Name FROM sys.tables WHERE Name = 'Users' AND is_tracked_by_cdc = 1)
    BEGIN
        EXEC sys.sp_cdc_enable_table
        @source_schema = N'dbo',
        @source_name   = N'Users',  
        -- @captured_column_list = N'[UserID], [Name], [IsDeleted]',
        @role_name     = NULL
    END


it will create below objects in database

- Tracking table cdc.dbo_Users_CT
- Table-valued Functions
    cdc.fn_cdc_get_all_changes_dbo_Users - Returns one row for each change applied to the source table within the specified log sequence number (LSN) range. If a source row had multiple changes during the interval, each change is represented in the returned result set.

    cdc.fn_cdc_get_net_changes_dbo_Users - Returns one net change row for each source row changed within the specified LSN range. That is, when a source row has multiple changes during the LSN range, a single row that reflects the final content of the row is returned by the function. For example, if a transaction inserts a row in the source table and a subsequent transaction within the LSN range updates one or more columns in that row, the function returns only one row, which includes the updated column values.

3) Finish CDC Enabled on dbo.Users table. Now, You can get changes on "dbo.Users" table using below syntax
  
    select * from cdc.dbo_Users_CT
  

    DECLARE @Begin_lsn BINARY(10);
    DECLARE @End_lsn BINARY(10);
    DECLARE @Operation_Time DATETIME=Getdate();
    DECLARE @LastUpdatedDate DATETIME=Getdate()-2
  
    SET @Begin_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@LastUpdatedDate);
    SET @End_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@Operation_Time);

    SELECT * FROM Cdc.fn_cdc_get_all_changes_dbo_Users(@Begin_lsn,@End_lsn,'all')

No comments:

Post a Comment