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