SQL Server 2016 Temporal Tables

A temporal table is a new type of user table in SQL Server 2016, it’s designed to keep a full history of data changes and allow easy point in time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine).

– Auditing all data changes and performing data forensics when necessary
– Reconstructing state of the data as of any time in the past
– Calculating trends over time
– Recovering from accidental data changes and application errors

getting-started-with-temporal

This post is when you already have tables in place in SQL Server. See the Microsoft article at the bottom of the article that explains how to create them from Scratch. If your like me you’ve been using SQL Server through the versions.

Create the history schema

CREATE SCHEMA history; 
GO

Converting an existing table

ALTER TABLE dbo.components
ADD ValidFrom datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_ValidFrom DEFAULT SYSUTCDATETIME(), 
 ValidTo datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_ValidTo DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'), 
 PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo); 
GO
ALTER TABLE dbo.components
 SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.components))

Querying the data

You now easily query the audit history of the components table by using FOR SYSTEM_TIME BETWEEN and then specify the dates.

SELECT * FROM dbo.components 
 FOR SYSTEM_TIME 
 BETWEEN '2017-05-19 00:00:00.0000000' AND '2017-05-19 00:00:00.0000000'

Converting back to a normal table

If you wish to remove system versioning apply the following:-

ALTER TABLE dbo.components SET (SYSTEM_VERSIONING = OFF)

Then turn off period definition – without turning this off you won’t be able to remove the ValidFrom and ValidTo columns/constraints.

ALTER TABLE dbo.components  DROP PERIOD FOR SYSTEM_TIME;

Then we can delete the DF_ValidFrom and DF_ValidTo constraints

ALTER TABLE dbo.components DROP CONSTRAINT DF_ValidFrom;
ALTER TABLE dbo.components DROP CONSTRAINT DF_ValidTo;

We can then remove the two ValidFrom and ValidTo columns

ALTER TABLE dbo.components DROP COLUMN ValidFrom
ALTER TABLE dbo.components DROP COLUMN ValidTo

You can then drop the history table from the history schema. Warning – this will remove all history – be careful!)

DROP TABLE history.components

Read more on how to use Temporal tables on Microsoft’s web site.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s