Sql-server – Change Data Capture vs trigger for logging changes

change-data-capturesql serversql-server-2016t-sqltrigger

I am going to create history table which is populated by trigger (after insert, update, delete). As only 20% of the columns are going to be updated, I decided to log only the changed values – if the values are not changed, NULL value is going to be used in the history table. For example:

The history table columns will be sparse and I am going to save a lot of space versus ordinary implementation which is logging all data (this is due to my test and my bussness cases).

As I the SQL Server 2016 SP1 standard edition supports Change Data Capture I am wondering are their any pros/cons/differences between using it and trigger-based logging?

I have check few artciles (here and here) and cannot see what more Change Data Capture can give me.

Best Answer

Given the choice between those two things, here's why I'd choose triggers:

Triggers can make related table changes synchronously (like if you need to update reporting tables or check referential integrity)
Triggers let you store the data in the format that works for you, whereas CDC puts it in its own CDC schema, in tables that may not work for your reports
Triggers can have logic that checks the kind of update or the source of the update, and only log things you want to log
Triggers can easily be temporarily disabled for things like bulk loads, or if the business is facing huge load demands (like Black Friday sales)
Triggers let you put more business logic in, like if you want to put some kinds of history in one table and other kinds of history in another
Triggers don't directly add storage overhead (whereas CDC adds at least 34 bytes per row)

Related Solutions

Sql-server – Change data capture – how to know who made the change

I filed a bug about this, but it was closed as "by design."

http://connect.microsoft.com/SQLServer/feedback/details/283707/cdc-options-to-capture-more-data-username-date-time-etc

Unfortunately you will have to use another technique (e.g. SQL Audit or a trigger) to get this information (and some custom code to try to correlate it to the CDC data as much as you can). I wrote about this deficiency also in my chapter "SQL Server Audit, Change Tracking, and Change Data Capture" in the book SQL Server MVP Deep Dives (volume 1).

I'm sorry that I don't have a better workaround for you but the built-in capabilities of CDC just aren't going to meet your requirements. :-(

Sql-server – Change Data Capture and the __$update_mask binary

And the moral of the story is... test, try other things, think big, then small, always assume there is a better way.

As scientifically interesting as my last answer was. I decided to try one other approach. I remembered I could do concat with the XML PATH('') trick. Since I knew how to get the ordinal of each changed column from the captured_column list from the previous answer I thought it would be worth testing if the MS bit function would work better that way for what we needed.

SELECT __$update_mask ,
        ( SELECT    CC.column_name + ','
          FROM      cdc.captured_columns CC
                    INNER JOIN cdc.change_tables CT ON CC.[object_id] = CT.[object_id]
          WHERE     capture_instance = 'dbo_OurTableName'
                    AND sys.fn_cdc_is_bit_set(CC.column_ordinal,
                                              PD.__$update_mask) = 1
        FOR
          XML PATH('')
        ) AS changedcolumns
FROM    cdc.dbo_MyTableName PD

It's way cleaner than (though not as fun as) all that CLR, returns the approach back to native SQL code only. And, drum roll.... returns the same results in less than a second. Since the production data is 100 times bigger every second counts.

I'm leaving the other answer up for scientific purposes - but for now, this is our correct answer.

Best Answer

Related Solutions

Sql-server – Change data capture – how to know who made the change

Sql-server – Change Data Capture and the __$update_mask binary

Related Question