When To Use Temporal Tables

Randolph West wraps up his temporal tables series by asking when you should use them:

Tracking changes to your data is a big deal, and in databases with heavy churn, you can end up needing a massive amount of space to handle your history, especially if you need to retain seven years of data, like some of our customers.

If data storage is a concern, I’m going to recommend SQL Server Enterprise Edition. The Books Online documentation specifically states that the history tables for Temporal Tables are implemented with Page Compression by default, which is an Enterprise Edition feature.

My quick thought is, use them when you want a type 2 dimension without putting in the effort to create a type 2 dimension—in other words, non-warehouse systems in which you need long-term data changes.

Related Posts

Building Temporal Tables From Existing Tables

Mala Mahadevan shows how you can convert an existing SQL Server table into a history table using temporal tables in SQL Server 2016: SQL Server has had many different methods to track changes to data. There has been old-fashioned trigger-based logging, Change Data Capture, and Change Tracking. All of these features allow you to see […]

Read More

Querying Data In Temporal Tables

Jeanne Combrinck shows us how to query data stored in temporal tables: When you want to get latest (actual) state of data in a temporal table, you can query the same way as you query a normal table. If the PERIOD columns are not hidden, their values will appear in a SELECT * query. If […]

Read More

Categories

December 2015
MTWTFSS
« Nov Jan »
 123456
78910111213
14151617181920
21222324252627
28293031