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

Automating Temporal Table Creation

Bill Fellows got an increasingly specific set of requirements about data collection: This post is another in the continuing theme of “making things consistent.” We were voluntold to help another team get their staging environment set up. Piece of cake, SQL Compare made it trivial to snap the tables over. Oh, we don’t want these tables in […]

Read More

Temporal Table Time Zones

Louis Davidson talks about how to translate temporal table start and end dates to your local time zone: In my first 5 blogs on Temporal, I failed to note something pretty important about their usage. The start and end times for the row (and it follows, the historical rows) are likely not in your local time […]

Read More

Categories

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