建立記憶體優化的系統版本的時態表並清理 SQL Server 歷史記錄表

當你想要控制命名並仍依賴系統建立具有預設配置的歷史表時，使用預設歷史記錄表建立臨時表是一個方便的選項。在下面的示例中，新的系統版本記憶體優化時態錶連結到新的基於磁碟的歷史記錄表。

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

清理 SQL Server 歷史記錄表隨著時間的推移，歷史記錄表會顯著增長。由於不允許從歷史表中插入，更新或刪除資料，因此清理歷史表的唯一方法是首先禁用系統版本控制：

ALTER TABLE dbo.Employee

SET（SYSTEM_VERSIONING = OFF）; 走

從歷史表中刪除不必要的資料：

    DELETE FROM dbo.EmployeeHistory

WHERE EndTime <=‘2017-01-26 14:00:29’;

然後重新啟用系統版本控制：

ALTER TABLE dbo.Employee

SET（SYSTEM_VERSIONING = ON（HISTORY_TABLE = [dbo]。[EmployeeHistory]，DATA_CONSISTENCY_CHECK = ON））;

清除 Azure SQL 資料庫中的歷史記錄表有一點不同，因為 Azure SQL 資料庫內建支援清理歷史記錄表。首先，需要在資料庫級別啟用臨時歷史記錄保留清理：

ALTER DATABASE CURRENT

設定 TEMPORAL_HISTORY_RETENTION GO

然後設定每個表的保留期限：

ALTER TABLE dbo.Employee

SET（SYSTEM_VERSIONING = ON（HISTORY_RETENTION_PERIOD = 90 天））;

這將刪除歷史記錄表中超過 90 天的所有資料。SQL Server 2016 內部部署資料庫不支援 TEMPORAL_HISTORY_RETENTION 和 HISTORY_RETENTION_PERIOD，並且在 SQL Server 2016 內部部署資料庫上執行上述兩個查詢之一，將發生以下錯誤。

對於 TEMPORAL_HISTORY_RETENTION，錯誤將是：

Msg 102, Level 15, State 6, Line 34

‘TEMPORAL_HISTORY_RETENTION’附近的語法不正確。

對於 HISTORY_RETENTION_PERIOD 錯誤將是：

Msg 102, Level 15, State 1, Line 39

‘HISTORY_RETENTION_PERIOD’附近的語法不正確。