Menu
What advantages do Temporal Tables have over Change Data Capture or Change Tracking in SQL Server?
695
A temporal table must have a primary key defined in order to correlate records between the current table and the history table, and the history table can't have a primary key defined. The SYSTEM_TIME period columns used to record the ValidFrom and ValidTo values must be defined with a datatype of datetime2.
Benefits of Change Data Capture Ultimately, CDC will help your organization obtain greater value from your data by allowing you to integrate and analyze data faster—and use fewer system resources in the process.
With minimal setup, these help answer so many questions we get, I can see plenty of both data/business use cases ("how many registrants did this conference have 90, 60, and 30 days before the event") but also many schema or framework level audit questions as well.
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?
79
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
