SQL Server Temporal Tables – “how to” summary

By | December 13, 2016

Are you looking for a quick “how to” summary for temporal tables, including the actual T-SQL syntax? Here it is. If you need a longer explanation, see my article on SQL Server Central or the MSDN documentation.

Creating temporal tables

CREATE TABLE ...
(
...
SysStartTime DATETIME2(0) GENERATED ALWAYS AS ROW START,
SysEndTime DATETIME2(0) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = ...));

Notes: You can choose any name for the columns. You can also choose any DATETIME2 precision you want. You don’t need to specify the name of the history table.

Making a table temporal

ALTER TABLE ... ADD
	SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT DF_Tablename_SysStartTime DEFAULT '1900-01-01 00:00:00',
	SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT DF_Tablename_SysEndTime DEFAULT '9999-12-31 23:59:59',
	PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

ALTER TABLE ...
	SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = ...));

Notes: You can choose any name for the columns. You can also choose any DATETIME2 precision you want. You don’t need to specify the name of the history table. The default value for SysStartTime could be any time in the past. The default value for SysEndTime must be the maximum DATETIME2 value.

Removing temporal from a table

ALTER TABLE [dbo].[Inventory] SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE [dbo].[Inventory] DROP PERIOD FOR SYSTEM_TIME;
ALTER TABLE [dbo].[Inventory] DROP COLUMN [SysStartTime], [SysEndTime];

Notes: If you have default-constraints on the period columns, you will have to delete the default-constraints first.

Managing history retention on SQL Azure temporal tables

ALTER DATABASE CURRENT
SET TEMPORAL_HISTORY_RETENTION ON

GO

ALTER TABLE [dbo].[Inventory]
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = ... DAYS));

Point-in-time queries

SELECT ...
FROM ...
FOR SYSTEM_TIME AS OF ...

Interval queries

SELECT ...
FROM ...
FOR SYSTEM_TIME BETWEEN ... AND
SELECT ...
FROM ...
FOR SYSTEM_TIME FROM ... TO
SELECT ...
FROM ...
FOR SYSTEM_TIME CONTAINED IN (..., ...)

Notes: The difference between the three interval queries is explained here.

Get all current and historical data

SELECT ...
FROM ...
FOR SYSTEM_TIME ALL

References