Temporal Tables – When To Use Them

This is the final part of my Temporal Table series. You can read parts one, two and three here.

Last week I demonstrated how to modify temporal tables in SQL Server 2016.

This week I’m answering a very simple question.

When should you use Temporal Tables?

I see two main reasons for using this feature:

  • You are legally obligated to store an audit trail of changes to your data.
  • You currently implement history tracking through triggers and stored procedures or in application code.

Considerations

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.

If you cannot afford Enterprise Edition, temporal tables will still work, but page compression will not. You may still have to spend money on additional storage.

Realistically, there’s no hard and fast estimate of how much space this feature will use. You will have to do some testing. Based on that testing, you can decide whether to spend money on additional storage, an Enterprise Edition licence, or both.

Whatever your decision, I hope that you have learned something. SQL Server 2016 is, in my opinion, as significant an update to the product as SQL Server 2005 was.

Author: randolph

Randolph West is a Microsoft Data Platform MVP, and has worked with SQL Server since the late 1990s. When not consulting, he can be seen acting on the stage and screen, or doing voices for independent video games. Connect with Randolph on Google+ or Twitter.