Why we require temporal table in SQL Server 2016 as we have CDC or CT?

Question

What advantages do Temporal Tables have over Change Data Capture or Change Tracking in SQL Server?

Gastric

Answer 1

CDC and change tracking really are not intended to solve historical data problems. As the names imply, change tracking or CDC tells you things changed and what the changes were. You can choose to do what you want with it and that may include persisting the data somewhere if you need to keep historical data. This includes capturing, tweaking (e.g. add some kind of timestamp to the schema), storing and managing the data and associated processes. It's workable but the effort is non-trivial and on-going.

Temporal databases provide native capabilities to manage historical data including query semantics. You have to roll your own potentially complex queries to get the same capabilities in a DB that has temporal capabilities (e.g. what would your query look like to answer the question: what was the average temperature across the 42 type-MCC2 sensors in sector C at 3pm on Aug 28, 2016?). This assumes you already have the data in a queryable format.

There are lots of good write ups on why temporal that you should check out: Why do we need a temporal database?

Wheeze

I am designing a new data-model in a community-driven wiki-like project of structured data (as opposed to plain text). I need to give the users the ability to edit any of the content and persist it, as well as being able to roll-back to previous versions etc. What would be the right choice CDC or TT? – Micromillimeter

Going purely with what you stated here, temporal seems like the better bet. Just like documents get versioned in sharepoint, your wiki-like project will have versions within the database based on committed writes. That said, it might not be easy to implement unless you really have just 1 or VERY few related tables. Else, making sure you revert to the correct point in time is not easy when you have to ensure sync across many tables. Point-in-time-restore might be better here but could take significantly longer. – Wheeze

Answer 2

A few notes that make Temporal Tables stand out.

Temporal tables give you an easy way to alter an existing query to ask for that query to "run at a specific previous time" giving the results that would have been seen at that time with absolute accuracy.

Temporal tables have actual time, not just order associated with them.

Temporal tables are available in all editions of SQL Server, not just the very expensive enterprise edition.

I hope this brought you value!

Matt Christenson

Doall