7

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!

5

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.

  • 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 '16 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 '17 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 '17 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. – David Spillett Jan 3 '18 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? – Shimmy Dec 26 '18 at 22:15

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.