Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted March 21, 2016

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

By Arshad Ali


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
  • 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.


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.


Temporal Tables

Temporal Table Considerations and Limitations

What's New in SQL Server 2016

See all articles by Arshad Ali

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM