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:
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;
SELECT * FROM TemporalTest FOR SYSTEM_TIME BETWEEN '2016-10-19 13:21:07.0000000' AND '2016-10-19 13:21:08.0000000';
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:
Here is a link the BOL for Temporal Tables:
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.