Microsoft SQL Server Temporal Tables Creating a Memory-Optimized System-Versioned Temporal Table and cleaning up the SQL Server history table
Example
Creating a temporal table with a default history table is a convenient option when you want to control naming and still rely on system to create history table with default configuration. In the example below, a new system-versioned memory-optimized temporal table linked to a new disk-based history table.
CREATE SCHEMA History
GO
CREATE TABLE dbo.Department
(
DepartmentNumber char(10) NOT NULL PRIMARY KEY NONCLUSTERED,
DepartmentName varchar(50) NOT NULL,
ManagerID int NULL,
ParentDepartmentNumber char(10) NULL,
SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH
(
MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA,
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = History.DepartmentHistory )
);
Cleaning up the SQL Server history table Over time the history table can grow significantly. Since inserting, updating or deleting data from the history table are not allowed, the only way to clean up the history table is first to disable system versioning:
ALTER TABLE dbo.Employee
SET (SYSTEM_VERSIONING = OFF); GO
Delete unnecessary data from the history table:
DELETE FROM dbo.EmployeeHistory
WHERE EndTime <= '2017-01-26 14:00:29';
and then re-enable system versioning:
ALTER TABLE dbo.Employee
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[EmployeeHistory], DATA_CONSISTENCY_CHECK = ON));
Cleaning the history table in Azure SQL Databases is a little different, since Azure SQL databases have built-in support for cleaning of the history table. First, temporal history retention cleanup need to be enable on a database level:
ALTER DATABASE CURRENT
SET TEMPORAL_HISTORY_RETENTION ON GO
Then set the retention period per table:
ALTER TABLE dbo.Employee
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 90 DAYS));
This will delete all data in the history table older than 90 days. SQL Server 2016 on-premise databases do not support TEMPORAL_HISTORY_RETENTION and HISTORY_RETENTION_PERIOD and either of the above two queries are executed on the SQL Server 2016 on-premise databases the following errors will occur.
For TEMPORAL_HISTORY_RETENTION error will be:
Msg 102, Level 15, State 6, Line 34
Incorrect syntax near ‘TEMPORAL_HISTORY_RETENTION’.
For HISTORY_RETENTION_PERIOD error will be:
Msg 102, Level 15, State 1, Line 39
Incorrect syntax near ‘HISTORY_RETENTION_PERIOD’.
