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
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