Kenneth M. Nielsen

New Features in SQL Server 2016 – Temporal Data Tables

July 6, 2015 by

There are many new features in SQL Server 2016, but the one we will focus on in this post is:

  • Temporal Database Tables

One of the many new features in SQL server 2016 is the built-in support for temporal data tables. This is a feature that many developers have been urging to have as a “out of the box feature” – and therefore many have developed their own ways of implementing the capability to store information about how data looked in a certain period of time.

What is a Temporal Data Table?

The concept of a temporal data table is that the table will hold information about a records value in any point in time – thus meaning that we easily can read from the table and get values that are not current, but merely a historical view of the record.

The table that is to hold temporal data, needs to contain system columns of datatype Datetime2, one holding the startdate and one holding the enddate, this will allow any application or a user to query the data and get values for a specific point in time.

Microsoft has decided to call temporal data tables “System-Versioned” tables when implementing them in SQL Server 2016.

Who needs Temporal Data Tables?

There is no such thing as data that are not dynamic, if data is not dynamic, the system/application is dead and we have other issues. All other applications or business users consuming data or creating data will know that they eventually will need to look at the historical data, some use cases for the temporal data tables would include:

  • Understanding how the business changes over time
  • Tracking data changes over time
  • Auditing all changes to data
  • Maintaining a slowly changing dimension for decision support applications
  • Recovering from accidental data changes and application errors
  • Only allowing part of the organization to look at updated records, whilst another look at the old version.

Business Intelligence

From a Business Intelligence developer viewpoint, this could solve some issues on collection historical data in i.e. Dimension Tables where many business analysts have a valid need to know how data looked at a point-in-time. This is especially an issue in regards to i.e. Organizational Dimensions and their hierarchy, which is bound to change over time. With the implementation of temporal data tables we get the feature out-of-the box, all we need to do is to configure the table.

Human Resource

Human resource data is by default Temporal, as an employee tends not to stay in the exact same state from day one, until either he/she is terminated or leaving for another job position. This is another clear use case for this feature.

How does it Work?

The way Microsoft have chosen to implement this design in SQL server 2016, is to have a new table nested under the table that we enable for system-versioning. So actually when you look at the table in SSMS, there will be a little icon on the left indicating that there is more to this table, as you can see in the figure 1.


Figure 1: Table indicating that system-versioning is enabled

Once we expand the table information, we discover that there is a new table under the hood, this table is named history, but can be named whatever you prefer. The history table will hold information on attributes from the main table, as well as a start and end date for the period, where the value in the attribute is valid. This is shown in figure 2.


Figure 2:The table information is now expanded

This means that whenever there is a change to any attributes in the main table, a copy of the old values paired with a start and an end date will be inserted into the history table. Thus giving you or the application the ability to look at data from the past.

Insert

When inserting new data in a table that is system-versioned, rows will be given the default enddate “9999-12-31 23:59:59” upon insertion, this will allow the server to maintain the data and always have the current dataset in the table at all times. Basically means that the record is open and valid.

Updates

When updating a record on a system-versioned table, the startdate will be updated to the UTC time from the transaction running the update statement. The enddate will be “9999-12-31 23:59:59” and the old attribute values will be inserted into the history table, with a enddate set to UTC time from the transaction running the update statement and the startdate will remain.

Deletes

When deleting a row in a system-versioned table, the current values will be stored in the history table, and the end date will be set to UTC time from the transaction running the delete statement. In the main table the row will be removed, and thus not show up in the datasets from that table. If we would like to see the data, we need to query the history table.

How to enable Temporal (system-versioned) on a table

How to get a table to be system-versioned is something that we have to script at the moment, and it’s a fairly easy task to script. The table itself need to have a start and end date defined before we can enable the feature on the table.

We will create the Product table that will have system-version enabled from the start:

Besides the ordinary table creation statement, we have added a few new lines to the create statement, the lines are these, and they will be explained.

This line declares that startdate attribute, should be of type datetime2 and always generated when data is either inserted, updated or deleted. Also we indicate that it should be of the type ROW START.

This line declares that enddate attribute, should be of type datetime2 and always generated when data is either inserted, updated or deleted. Also we indicate that it should be of the type ROW END.

Now this last statement is the PERIOD that tells us which attributes are to be handled as a start and an end of the period.

The table have the startdate (startdate) and enddate (enddate) defined, and upon creation the historytable called [dbo].[Producthistory] is created.

Now the table is ready for the data let us insert some test data into it:

Now if we select the data from product, we will have the following dataset at hand:

Product_id Category_id Product_Name startdate enddate
1 1 Toys 2015-06-27 14:21:24 9999-12-31 23:59:59
2 2 Bikes 2015-06-27 14:21:24 9999-12-31 23:59:59
3 3 Clothes 2015-06-27 14:21:24 9999-12-31 23:59:59
4 4 Cars 2015-06-27 14:21:24 9999-12-31 23:59:59

As you can see all records is the current ones, indicated by the enddate set to “9999-12-31 23:59:59”

If we query the table holding the history, it will give us an empty dataset, because none of the original records has been updated or deleted.

Now, let us try to update a record, and see what happens to our data:

When we again select from our table dbo.Product, we can see that the record in fact have changed. But if we look closely at the record we updated, you can see that the startdate has changed from the initial “2015-06-27 14:21:24” to “2015-06-27 14:31:32” – this means that the value for this exact record is valid in a period of 10 minutes:

Product_id Category_id Product_Name startdate enddate
1 1 Toys 2015-06-27 14:21:24 9999-12-31 23:59:59
2 2 Bikes 2015-06-27 14:21:24 9999-12-31 23:59:59
3

3

Clothes 2015-06-27 14:21:24 9999-12-31 23:59:59
4 4 Christmas Accesories 2015-06-27 14:31:32 9999-12-31 23:59:59

If we look at the dbo.ProductHistory table, we can now see that a new record has been inserted, and here we will see that a value for a record with an id = 4 will be “Cars” in a period of 10 minutes:

Product_id Category_id Product_Name startdate enddate
4 4 Cars 2015-06-27 14:21:24 2015-06-27 14:31:32

This means that we can query our history tables if we need to know an exact value for a specific point in time.

See more

To investigate more, you can download ApexSQL Complete, a free SSMS and Visual Studio add-in, which supports all temporal table options.


Kenneth M. Nielsen

Kenneth M. Nielsen works as managing consultant and team lead for the company Rehfeld Partners in Denmark. He has worked at various consulting firms and worked on many small/large/very large BI installations in Denmark over the last 12 years.

He really likes to advise the customers to take the right decisions, but also maintains a high technical knowledge, so he can act as both architect and developer.

Over the last years, he has become a highly-rated international speaker at various SQL events. Organizing the Danish SQLSaturday and member of the board in SQLSUG.dk

View all posts by Kenneth M. Nielsen
SQL Database development, SQL Server 2016

About Kenneth M. Nielsen

Kenneth M. Nielsen works as managing consultant and team lead for the company Rehfeld Partners in Denmark. He has worked at various consulting firms and worked on many small/large/very large BI installations in Denmark over the last 12 years.

He really likes to advise the customers to take the right decisions, but also maintains a high technical knowledge, so he can act as both architect and developer.

Over the last years, he has become a highly-rated international speaker at various SQL events. Organizing the Danish SQLSaturday and member of the board in SQLSUG.dk

View all posts by Kenneth M. Nielsen

3,114 Views