10

It seems to be quite difficult to find comparisons between system-versioned temporal tables and the older options, such as DB triggers and CDC. I currently don't have the time to write an extended test on SQL Server 2016, so I thought I'd ask about it here.

Basically, the typical advantage with triggers is that they are easier to manage in stand-alone and clustered / alwaysOn environments, are real-time for being synchronized, and have access to session data such as the user ID.

CDC on the other hand while requiring a bit more management and being asynchronous, is much lighter, and thus performs far better. So if there's any doubt at all that the bottle necking caused by triggers could become a problem, CDC will basically be the superior solution. In terms hardware requirements, there's a negligible extra space requirement by CDC due to using logs and cdc audit tables for tracking the changes.

The question: How do temporal tables compare to the two above? In terms of speed, performance, storage space usage. WHEN should I use temporal tables instead of triggers or CDC? When should I not?

I understand anything as potentially complex as the business requirements and technical limitations behind DB auditing isn't going to have one easy answer, as it depends largely on the requirements and scope of the project. But anything to shed more light on the questions above would be appreciated. Thanks!

(Note, I got back to this in 2021 and added what I had learned as an answer to the original question. You'll find it below.)

2
  • It looks like your 2021 edit should really be an answer not an edit to your question. I'd upvote it. Aug 31, 2021 at 16:12
  • Thanks for the comment @JustinCave, I won't change the original accepted answer as that would take away deserved credit, but I'll add the edit as one.
    – Kahn
    Sep 9, 2021 at 10:39

2 Answers 2

6

It depends on your business case, Temporal tables and change data capture offer different functionality.

Temporal tables are used to provide a version of your table at a point in time. A use case might be a slowly changing dimension where you want to track the changes in dimension attributes and report them from any moment in time.

Change data capture might be used on an OLTP table, to allow you to easily facilitate the export to a data mart. It logs all changes to a separate table, so you can easily view changed rows since your last export LSN point.

5
  • Thanks, however I am still most interested in the technical performance and storage requirements compared to the other solutions, as they will give me the best idea of how to apply them to the business requirements of this case.
    – Kahn
    Oct 19, 2016 at 10:38
  • But they have as said totally different use cases. CDC tracks data changes, for example to send them to a data warehouse. The old data is not available anymore in the table. Temporal tables are used to allow access to old data, for example in said data warehouse (which may well get updated from a CDC captured data). Reposting systems often need to access historical data contexts (give me a risk report FROM last sunday Thursday - with the data as seen last thursday). Temporal Table. Recording changes - CDC. Focus on the extreme differences in functionality.
    – TomTom
    Nov 18, 2017 at 7:50
  • @Kahn I landed here looking for details about the internals of temporal table storage. My hope was that there was some kind of "diff" being done, similar to what version control systems do, but it appears history tables are stored like regular tables. The one storage optimization detail I've found states By default, the history table is PAGE compressed.
    – John Mo
    Dec 8, 2017 at 19:12
  • Temporal tables are an example of where page compression can make a huge difference: because the whole row is stored for each version and more often than not the amount of change is small there is a lot of opportunity to save space by removing duplication. Even if everything changes, any unicode strings will get compacted which can save a lot. Note that the compression doesn't touch off-page values at all, so using NVARCHAR(MAX) or VARBINARY(MAX) (or the older TEXT/NTEXT/IMAGE) can make temporal tables consume huge amounts of space. Jan 3, 2018 at 13:51
  • Hi folks. In my scenario I'm developing a community driven wiki-like website of structured data, where I need to be able to roll-back to previous versions. What would be the best choice CDC or TT? Dec 26, 2018 at 22:15
3

Since there's been some ongoing interest to this and years later I've become quite well acquainted with all of the above, here's a short summary in performance terms: I did a test on some version of SQL Server 2016 involving inserting, updating, and deleting 10000 rows from 40 different types of tables one by one, and charted the overall time spent, basic locking info etc. about each. The simple summary is that where triggers added on average 500-1000% more delay to the operations, with temporal tables and CDC it was closer to 10% extra delay per operation. Would help if I had the exact results but I don't remember them anymore. The trigger process was very streamlined, but inserted one row per changed column, vs. temporal / cdc which inserted one row regardless of how many columns in it were changed. In this sense, some changes could have made the triggers seem slower than they were, because of the key contention of multiple rows being inserted at the same time. Nevertheless, it was obvious triggers were the least suited tool for simple auditing. So here's a simple technical rundown of the differences I was trying to understand when I created this post:

Triggers are only ever good if you really need some custom logic built into the DB, to watch over DML changes, modify specific data, capture the userid in specific instances, etc. But try to avoid them like the plague. They're horrible for performance. And if you need auditing or logging, they're the last place you should look.

Temporal tables are very easy to manage once you get them running especially in HADR like Always On. As they support compression and reflect most schema changes from parent to history table, so they require very little upkeep. Especially with new SQL Server versions you can set the retention period to remove data older than x years anyway so the storage and cleanup considerations are negligible as well. They are as fire & forget as things come, barring some exotic updates to parent tables where you need to change the data, in which case you have to de-link, modify the parent and history table, and link them again. But these are rare and relatively easy to do. The temporal table package is robust and handles errors well so you'll find it hard to break by accident.

CDC then can be great for reporting services or similar scenarios where you don't mind asynchronous data, but you need to analyze changes for example in nightly batches. You can set the retention setting to only keep x days of data to keep the storage costs to a minimum. That said, CDC is to my experience finicky and not very stable. DML's can "break" it sometimes with no warning, so you might need db-level DDL triggers to warn you of changes to objects tracked by CDC. You might also need to set custom watchjobs for HADR as it does not natively handle failover events. And CDC has a very nasty propensity for failing to restart after being disabled, something about the state of it not being updated correctly using MS own jobs. This means it will occasionally require manual work to make sure the cleanup and capture jobs and their references are removed correctly. That said, SSIS / RS integrate very well and make using CDC easy for them.

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.