Getting Started with Temporal Table in SQL Server 2016 – Part 1

Introduction

SQL Server 2016 introduces the temporal table (also known as ‘system-versioned’ temporal table) feature to store history of data changes in a table so that you can travel back in time and get the data that represents a past state in time. In this article series, I am going to discuss this new feature in detail, including scenarios where you can leverage this new feature, demonstrate creating a new table with this feature and demonstrate enabling this feature for an existing table, etc.

Temporal Table – What Is It

Temporal table is a new built-in feature in SQL Server 2016 that allows you to travel back in time and get the data that is represented at a past state in time rather than the data that is correct at the current moment in time.

When a table is created as a temporal table or an existing table is modified to be a temporal table, SQL Server 2016 internally maintains two tables – one for the current data and the other one for the historical data storage. SQL Server 2016 also includes querying constructs to query data from different timestamps (correct information about stored facts at any given point in time) and at the same time it hides the complexity from users.

These are some of the common scenarios where use of the temporal table feature will simplify development and maintenance:

  • To understand how data changes over time with on-ongoing business and to calculate data change trends over time
  • Querying data as of a particular point in time to examine the state of the data at that given point in time
  • As it allows tracking and auditing of data changes over time, it helps in meeting regulatory compliance and to do data forensics when needed
  • Implementing and maintaining slowly changing dimensions in a data warehouse scenario
  • Reverting a table to the “last known good state” without downtime – helps in a scenario when someone (or because of some application errors) accidentally deletes data and you want to revert or recover it

The biggest advantage of the temporal table feature is, it’s an in-built, out-of-the box feature. It eliminates the need to create custom handling of data change tracking over time in your applications. Unlike earlier custom implementations, with this feature you just don’t need to come up with different workarounds to retrieve historical data. Now you just need to focus on the business logic of your solution and use the newly introduced query construct to query the data, minimizing the effort needed for development and maintenance.

Temporal Table – How Does It Work

For a table with the temporal feature enabled, SQL Server internally manages two tables: a current table and a history table. The current table contains the current state of each row whereas the history table contains each previous value for each row after modification, if any, and the start time and end time for the period for which it was valid.

Old versions moves to history table

Apart from that, SQL Server 2016 also introduces query constructs to query data from these above two tables in a single query. For example, the SELECT statement FROM <table> clause has a new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses (discussed in detail later in this article series) to query data across the current and history tables. This new SELECT statement syntax is supported directly on a single table, propagated through multiple joins, and through views on top of multiple temporal tables:

Include historical versions 

Temporal Table – Pre-requisites and Considerations

There are certain pre-requisites and limitations with the usage of a temporal table. Here are a few of the important ones; a detailed list of these considerations and limitations can be found here.

  • A temporal table must have a primary key defined in order to correlate records between the current table and the history table
  • History table cannot have constraints (primary key, foreign key, table or column constraints) and triggers
  • AFTER triggers are allowed on current table only but INSTEAD OF triggers are not allowed on either current table or history table
  • Two columns for recording start and end date of changes should be defined with data type of datetime2 to define the period of validity for each record (declared as GENERATED ALWAYS AS ROW START / END). These columns are referred to as SYSTEM_TIME period columns. These columns can be marked as HIDDEN to hide it from end users
  • Data movement from the current table to the history table is SQL Server’s responsibility and hence you cannot modify data in the history table directly
  • INSERT and UPDATE statements cannot reference the SYSTEM_TIME period columns as they are internal for data change tracking purpose
  • Regular queries only affect data in the current table. To query data from the history table, you must use temporal query constructs as discussed later in this article series
  • TRUNCATE TABLE or DROP TABLE is not supported while SYSTEM_VERSIONING is ON
  • Current and history table cannot be FILETABLE or FILESTREAM

Temporal table is supported with memory optimized tables as well; you can refer this msdn article for more detail.

Note: The feature mentioned and demonstrated in this article is based on SQL Server 2016 Release Candidate and might change when RTM will be available or in future releases.

Conclusion

SQL Server 2016 introduces the Temporal table (also known as ‘system-versioned’ temporal table) feature to store history of data changes so that you can travel back in time and get the data that represents a past state in time rather than the data that is correct at the current moment in time.

In this article, I talked about this new feature, how it works, considerations and limitations to keep in mind when using this new feature. In the next article of the series, I am going to demonstrate creating a new table with this feature or modify an existing table to enable this feature, querying data from a temporal table or system-versioned table with newly introduced query constructs, schema modification consideration, meta-data queries, etc.

Resources

Temporal Tables

Temporal Table Considerations and Limitations

What’s New in SQL Server 2016

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles