Change Data Capture For Updates And Deletes Only

Our database is insert inensive (200-500k per night) but update light (maybe a few hundred per day). I need to preserve a history of all changes to the inserted rows themselves fo

Solution 1:

I would just use a trigger to capture updates and deletes.

I don't think you can tell CDC what DML to pay attention to, and I think it's quite wasteful to let CDC record all of these inserts only to delete them afterward. That in and of itself is expensive and the fragmentation it will cause will also cause issues for any queries you run against the capture tables (you'll have lots of mostly-empty pages) as well as the work statistics will have to do to constantly keep the stats up to date.

You could possible put an instead of insert trigger on the capture table, that just does nothing, but I haven't tried to do this to even see if it is allowed, and I certainly don't know what impact that will have on the CDC functions. Possibly worth some investigation, but my original answer still stands even if this hack does work: just use a trigger.

Solution 2:

If space is a consideration, you can always assign the CDC tables to work with a different filegroup that could potentially live on a different server. You'd do that this way:

ALTER DATABASE YourDatabase
 ADD FILEGROUP [cdc_ChangeTables];
 go

 --this step requires going on to somewhere on your hard drive and creating a folderALTER DATABASE YourDatabase
 ADD FILE ( NAME = N'cdc_ChangeTables',
            FILENAME = N'E:\NameOfFolderYouSetUp\YourDatabase_cdc_ChangeTables.mdf',
            SIZE =1048576KB,
            FILEGROWTH =102400KB )
 TO FILEGROUP [cdc_ChangeTables];
 GO

Then when you want to set up your CDC tables, you point them toward that filegroup instead:

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'TableYouWantToCapture',
    @role_name = N'cdc_admin',
    @filegroup_name = N'cdc_ChangeTables', --this is where you name the filegroup from previous step@supports_net_changes =1,  
    @capture_instance = N'dbo_TableYouWantToCapture',
    @captured_column_list ='ColumnName1, ColumnName2'; --comma-delimited list of column names
GO

If you want to query only updates/deletes, you can use the system function like so:

SELECT*FROM cdc.fn_cdc_get_all_changes_dbo_TableYouWantToCapture(@from_lsn, @to_lsn, N'all update old')
WHERE __$operation IN (1,3,4)