Temporal tables

What is a Temporal Table?

• A system versioned user table that maintains the entire history of data changes
• Introduced SQL:2011, proposed 1992
• Active data table is mirrored by historical mirror table controlled by database

Temporal Table Details

• Two timestamps columns are required to establish record validity for a period
• Tracking is at record level
• Designed to be compatible with current data models, code, and tools
• Supported by all major databases (Postgres, Microsoft SQL Server, MariaDB, Oracle, IBM DB2, etc)

Why Temporal Tables?

• Reconstruction of application at any point is fully realizable
• Auditing data changes
• Soft deletes are built in
• Investigation and data analysis is immediately available
• Application tuning for hot/cold storage

Example structure: SQL 2016

• Two timestamps columns are required to establish record validity for a period
• Tracking is at record level
• Designed to be compatible with current data models, code, and tools

CREATE TABLE [dbo].[HappyPlaces]
([Id] [INT] NOT NULL IDENTITY(1,1) CONSTRAINT [PK_HappyPlaces_Id] PRIMARY KEY,
[Name] NVARCHAR(100) NULL,
[ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
[ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME ([ValidFrom],[ValidTo]))
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[HappyPlacesHistory]));

Insert some data

INSERT INTO [dbo].[HappyPlaces] ([Name])
VALUES 
  ('Ilulissat'),
  ('Bora-Bora');

Query

SELECT *
FROM [dbo].[HappyPlaces]

• Current SQL query code on active table remains the same
• Expanded query space to access temporal records with operators on Timestamp columns

Results

[
  {
    "Id": 1,
    "Name": "Ilulissat",
    "ValidFrom": "2021-05-25T03:17:33.6257877",
    "ValidTo": "9999-12-31T23:59:59.9999999"
  },
  {
    "Id": 2,
    "Name": "Bora-Bora",
    "ValidFrom": "2021-05-25T03:17:33.6257877",
    "ValidTo": "9999-12-31T23:59:59.9999999"
  }
]

Query Expanded

Delete a record (poor Ilulissat melted)

DELETE 
FROM [dbo].[HappyPlaces]
WHERE [Name] = 'Ilulissat';

Query the table with normal syntax

SELECT *
FROM [dbo].[HappyPlaces];

Results

[
  {
    "Id": 2,
    "Name": "Bora-Bora",
    "ValidFrom": "2021-05-25T03:17:33.6257877",
    "ValidTo": "9999-12-31T23:59:59.9999999"
  }
]

It's gone, but is it really, use the expanded temporal keyword syntax to get the full history against the common table

SELECT *
FROM [dbo].[HappyPlaces]
FOR SYSTEM_TIME
ALL;

Results

[
  {
    "Id": 2,
    "Name": "Bora-Bora",
    "ValidFrom": "2021-05-25T03:17:33.6257877",
    "ValidTo": "9999-12-31T23:59:59.9999999"
  },
  {
    "Id": 1,
    "Name": "Ilulissat",
    "ValidFrom": "2021-05-25T03:17:33.6257877",
    "ValidTo": "2021-05-25T03:22:48.1764475"
  }
]

Ilulissat is in the history which is the union of the current active table and it's history table.  Look carefully at the valid datetimes corresponding to when the Ilulissat record was active in the primary table.  This will give you a complete history of any particular record

Awesome!  This is only one option under the temporal keywords explore the others   and see what you come up with

• AS OF <date_time>
• FROM <start_date_time> TO <end_date_time>
• BETWEEN <start_date_time> AND <end_date_time>
• CONTAINED IN (<start_date_time> , <end_date_time>)
• ALL

Convert a table to temporal

Example table (note you must have a primary key defined for the table)

CREATE TABLE [dbo].[HappyPlaces](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[HappyPlaces] ADD  CONSTRAINT [PK_HappyPlaces_Id] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
GO

Add the temporal columns and the history table

ALTER TABLE [dbo].[HappyPlaces]
	ADD	[ValidFrom] [DATETIME2](7) GENERATED ALWAYS AS ROW START NOT NULL DEFAULT SYSUTCDATETIME(),
		[ValidTo] [DATETIME2](7) GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CAST('9999-12-31 23:59:59.9999999' AS DATETIME2),
		PERIOD FOR SYSTEM_TIME ([ValidFrom],[ValidTo]);
GO

ALTER TABLE [dbo].[HappyPlaces]
	SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[HappyPlacesHistory]));
GO

The temporal accords are in effect now :)