SQL 2016 - Temporal Tables - What are they and how do you set one up?

[KevinConan]

First published on MSDN on Nov 01, 2016
What are Temporal Tables?



This is the start of a series of posts about one of my favorite new features of SQL 2016, Temporal Tables.  Temporal Tables allow you go back in time and see what the data in table looked like at that point in time.  Unlike the movie series Back to the Future, you do not need a flux capacitor, you just need any edition of SQL 2016 and yes, that includes Express!

It's important to note that applications only need to be mindful of two extra fields if they use select *, otherwise Temporal Tables are used just like regular tables for transactions!

You can find the list of Programmability Features included in each edition of SQL 2016 here .



If there is no flux capacitor, how do Temporal Tables work?



There are two main pieces to Temporal Tables, a history table is created with the same fields as the regular table and two new fields are added to track the period start and end of when the record was live.

Let's take a closer look at the history table.  When you update a record, the current version of it gets its period end stamped with the date and time and moved to the history table and the new version of the record is created in the main table with the current date and time as the period start.  If you delete a record, it is removed from the main table and added to the history table with the end period set the current date and time.  Here is a picture to give you an idea of how it works:





Now let's talk about the period start and end fields.  These fields store the start and end of when the record was live in the main table.  When you query the Temporal Table to see how the data looked at a certain point in time, these are the fields that are queried against.  Again, a picture is worth a thousand words:





Code Example time!



Let's jump right into creating a Temporal Table:

CREATE TABLE [dbo].[TemporalTest]
(
	[colint] [int] NOT NULL,
	[period_start] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
	[period_end] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
	PRIMARY KEY CLUSTERED 
	(
		[colint] ASC
	)ON [PRIMARY], PERIOD FOR SYSTEM_TIME ([period_start], [period_end])
) ON [PRIMARY]
WITH
(
	SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[TemporalTestHistory] , DATA_CONSISTENCY_CHECK = ON )
)

Here is how it looks in SSMS:





I did add more to our Temporal Table than the minimum requirements.  A clustered index and setting DATA_CONSISTENCY_CHECK = ON are both not required.  As a rule of thumb, I prefer to have a clustered index on every table (there are of course exceptions) and if there is an issue with the data consistency, I want errors to happen to stop changes before the problem gets worse.

We have not talked about SYSTEM_TIME yet (this part of the create table: PERIOD FOR SYSTEM_TIME ([period_start], [period_end])).  The SYSTEM_TIME is used when you want to query the Temporal Table for a specific date and time range.  We will see an example of how to use it later in this post.

Next, let's add some data, modify it and then query it a couple of different ways.

Wait a couple of minutes between running each of these commands so that the temporal time stamps are different

INSERT INTO TemporalTest (colint) VALUES (1), (2), (3), (4), (5);
 
DELETE FROM TemporalTest WHERE colint IN (2,4);
 
UPDATE TemporalTest SET colint = 7 WHERE colint = 5;
 
INSERT INTO TemporalTest (colint) VALUES (9), (11);

Now let's do a simple select to see all the data in both the live and history tables.

SELECT 'Live Table' AS 'Source', *
FROM TemporalTest
 
UNION ALL
 
SELECT 'History Table' AS 'Source', *
FROM TemporalTestHistory;

So you can see how the deleted records the before image of the record we inserted are stored in the history table.

Here is one way that you see how the data above looked between 2016-10-19 13:21:07.0000000 and 2016-10-19 13:21:08.0000000:

SELECT * FROM TemporalTest
FOR SYSTEM_TIME BETWEEN '2016-10-19 13:21:07.0000000' AND '2016-10-19 13:21:08.0000000';

So this shows us how the data looked after we deleted the odd numbers but before we updated the 5 to a 7.



Other Things to Consider with Temporal Tables



The history table of a temporal table can get pretty big if the temporal table has frequent inserts, updates and/or deletes.  You may want to consider using one or more of the following to help reduce the amount of local disk space needed for the history table:

• Row or Page Compression (Enterprise Edition only feature)
• Purging older data from the History Table
• Using Stretch DB on the History Table (any edition of SQL Server 2016 or newer)

The Wrap Up



This is the end of the first post in a short series about Temporal Tables.  In future posts, we will look at topics like how to drop a temporal table and can you update the schema of a temporal table among others.

Here is a link to the BOL for Create Table:

https://msdn.microsoft.com/en-us/library/ms174979.aspx

Here is a link the BOL for Temporal Tables:

https://msdn.microsoft.com/en-us/library/dn935015.aspx



Links to other posts in this series

SQL 2016 – Temporal Tables – How do you drop a Temporal Table?

SQL 2016 – Temporal Tables – Can you change the schema?