Last edited on 18 December 2019.
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 prior to SQL Server 2016 Service Pack 1.
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.