SQL 2016 Temporal Tables

— By Lori Brown @SQLSupahStah

A temporal table is a table that holds old versions of rows from a base table. In other words it is a history table.  By having temporal tables SQL Server can automatically manage moving old row versions to the temporal table every time a row in the base table is updated.  The temporal table is physically a different table then the base table, but is linked to the base table.

If you have ever needed to make business decisions based on insights from data that has evolved, you might need temporal tables.

Reasons to use temporal tables are…

• Auditing data changes and performing data forensics
• Reconstructing state of the data as of any time in the past
• Calculating trends over time
• Maintaining a slowly changing dimension for decision support applications
• Recovering from accidental data changes and application errors

Below I have created a base temporal table, have designated the history table and have set system-versioning on with data broken up by the RecValidFrom & RecValidTo columns. These columns are equivalent to a start time and end time to the history table and are used to define the period that the record is valid.

CREATE TABLE dbo.EmpTemporal

(ID INT PRIMARY KEY

,FirstName VARCHAR(30)

,LastName VARCHAR(50)

,Salary INT

,City VARCHAR(30)

,RecValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL

,RecValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL

,PERIOD FOR SYSTEM_TIME (RecValidFrom,RecValidTo)) WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmpTemporalHistory))

GO

Let’s add some records and see that they look like in the base table.

INSERT INTO dbo.EmpTemporal(ID, FirstName, LastName, Salary)

VALUES (1, ‘Lori’, ‘Brown’, 50000)

,(2, ‘Lucy’, ‘Lu’, 50000)

,(3, ‘Joe’, ‘Blow’, 50000)

,(4, ‘Bob’, ‘Barker’, 50000)

,(5, ‘Adam’, ‘West’, 50000)

GO

SELECT * FROM dbo.EmpTemporal

GO

Now let’s make changes to the base table so that we can see how data changes over time…

UPDATE dbo.EmpTemporal     — Lucy’s last name is changed

SET LastName = ‘Stewart’

WHERE ID = 2

UPDATE dbo.EmpTemporal     — Lori got a raise

SET Salary = 55000

WHERE ID = 1

DELETE dbo.EmpTemporal     — Bob Barker quit and was deleted

WHERE ID = 4

UPDATE dbo.EmpTemporal     — Adam moved      

SET City = ‘West Memphis’

WHERE ID = 5

UPDATE dbo.EmpTemporal    — Lucy also got a raise

SET Salary = 55000

WHERE ID = 2

We can query data in either the base table or the history table or from both using the FOR SYSTEM_TIME function with a valid time range to filter on.

SELECT * FROM dbo.EmpTemporal

SELECT * FROM dbo.EmpTemporalHistory

SELECT * FROM dbo.EmpTemporal

FOR SYSTEM_TIME

BETWEEN ‘2016-01-01 00:00:00.0000000’ AND ‘2016-04-01 00:00:00.0000000’

WHERE ID = 2

ORDER BY RecValidFrom

Limitations:

• Temporal tables must have a primary key.
• History table cannot have constraints (primary key, foreign key, table or column constraints).
• INSERT and UPDATE statements cannot reference the SYSTEM_TIME period columns.
• TRUNCATE TABLE is not supported while SYSTEM_VERSIONING is ON. You can disable by simply turning SYSTEM_VERSIONING = OFF.

ALTER TABLE dbo.EmpTemporal SET (SYSTEM_VERSIONING = OFF)

• Direct modification of the data in a history table is not permitted.
• ON DELETE CASCADE and ON UPDATE CASCADE are not permitted on the current table.
• Usage of replication technologies is limited.

More limitations can be found here…. https://msdn.microsoft.com/en-us/library/mt604468.aspx

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!