3

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:

enter image description here

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.

Improve this question
1
  • Are all your base table columns NOT NULL - otherwise, a change from 20 to NULL and from 20 to 20 BOTH show NULL in your demo history table. Also, did you build a view or table valued function that will give you the "at time X, the base table looked exactly like this" answer you'll need to answer questions, that works even when the base table column was actually NULL if that's allowed? – Anti-weakpasswords Jan 27 '18 at 2:54
6

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)
Improve this answer
6

@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.

Improve this answer
1
  • 2
    I think you and Brent have both presented great reason for each technology I prefer CDC because it's used in my environment extensively. Once you get the hang of the schema and where to find information that you need, it's really incredible. – Kris Gruttemeyer Dec 12 '16 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.