Change Data Capture vs trigger for logging changes

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.

@Brent Ozar has listed pros of using trigger (which are good and valid points), but to make an informed decision, I'd like to list some pros of CDC.

• Trigger is closely coupled (or synchronized) with your DML transactions, while CDC is not, because CDC is implemented via reading log by a separate sql job. The result is that your DML transactions are not impacted by your trigger's performance.

• CDC is more scalable if you want to implement change data logging to multiple tables while triggers may need lots of coding work.

• Trigger is more headache to DBAs (such as not aware of it or during trouble-shooting time) while CDC is less so.

• CDC can also easily be disabled on individual tables (trigger does not demo any advantage here IMHO)

At the end of the day, I'd say trying to run some boundary cases such as bulk data change (update/delete/insert) in both trigger and CDC context and see whether there is any concern to you, like performance, management convenience etc, and make your final call.

StackExchange Database Administrators Q#157893, answer score: 9