管理系統版本化時間表中的歷史數據保留

適用於： SQL Server 2016 (13.x) 和更新版本 Azure SQL Database Azure SQL 受控執行個體 Microsoft Fabric 中的 SQL 資料庫

系統版本化的時間表會將每一列的前一個版本都保留在歷史表中。歷史資料表在以下條件下，可能會比一般資料表增加更多資料庫大小：

你會長時間保留歷史資料。
您的資料修改模式以大量更新或刪除作業為主。

龐大且持續增長的歷史資料表可能會成為一個問題，因為它不僅會增加儲存成本，也會對時態查詢造成效能負擔。為歷史資料表制定資料保留政策，是規劃和管理每個時態資料表生命週期的重要部分。

規劃資料保留政策

要管理時間表資料保留，首先確定每個時序表所需的保留時間。在大多數情況下，你的保留政策應該是使用時序表的應用程式業務邏輯的一部分。例如，資料稽核與時間旅行場景中的應用，對於歷史資料必須有多長的時間以供線上查詢有明確要求。

確定資料保留期限後，制定管理歷史資料的計畫。決定歷史資料的儲存方式和位置，以及如何刪除早於保留需求的歷史資料。

本文中的每個方法都作用於目前表格中對應週期末的欄位，該欄位即 ValidTo 後述範例中的該欄位。每個資料列的期間結束值決定資料列版本何時「關閉」，也就是何時落入歷程記錄資料表。例如，該病症 ValidTo < DATEADD (DAY, -30, SYSUTCDATETIME()) 與超過 30 天前的歷史資料相符。

請選擇以下其中一種方法來對這些資料列採取動作：

Approach	運作原理	何時使用它
時間歷史保留政策	你可以為每個資料表設定保留期限，背景任務會自動刪除過時的列。	最簡單的方法是直接刪除過時的歷史。
資料表分割	滑動視窗會把最舊的分割區從歷史表中切換出來，讓你可以歸檔或丟棄它。	當你想在移除前先存檔歷史資料，或是想要消除分割區以進行時間查詢時，
自訂清除指令碼	排程腳本會停用系統版本控制，將舊資料列分段刪除，然後重新啟用系統版本控制。	當您的資料表無法使用保留原則，且無法進行分區時，

本文中的分割與自訂清理範例使用了「建立系統版本化時空表」文章的範例。

使用時間歷史保留政策

適用於：SQL Server 2017（14.x）及更新版本、Azure SQL Database、Azure SQL 受控執行個體，以及 Microsoft Fabric 中的 SQL 資料庫。

你可以在個別資料表層級設定時間歷史保留，這樣就能建立彈性的老化政策。為了實現時間保留，可以在建立資料表或架構變更時設定 HISTORY_RETENTION_PERIOD 。

定義保留政策後，資料庫引擎會執行排程背景任務，尋找並透明移除期間結束值比保留期更早的歷史列。

如何設定保留原則？

設定時態表的保留原則之前，請檢查是否已在資料庫層級啟用時態歷史保留功能：

SELECT is_temporal_history_retention_enabled,
       name
FROM sys.databases;

資料庫標誌 is_temporal_history_retention_enabled 預設為 ON，但你可以透過陳述 ALTER DATABASE 式來更改。資料庫引擎也會在時間點還原（PITR）作業後，自動將其設為 OFF，如時間點還原考量所述。若要為資料庫啟用時態歷史保留清除功能，請執行下列陳述式。請用你想更改的資料庫替換 <myDB> ：

ALTER DATABASE [<myDB>]
    SET TEMPORAL_HISTORY_RETENTION ON;

Important

即使 is_temporal_history_retention_enabled 為 OFF，你仍可設定時態資料表的保留期，但在該情況下，資料庫引擎不會自動觸發對過舊資料列的清理。

你可以在建立資料表時，透過設定 HISTORY_RETENTION_PERIOD 參數值來設定保留政策：

CREATE TABLE dbo.WebsiteUserInfo
(
    UserID INT NOT NULL PRIMARY KEY CLUSTERED,
    UserName NVARCHAR (100) NOT NULL,
    PagesVisited INT NOT NULL,
    ValidFrom DATETIME2 (0) GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 (0) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.WebsiteUserInfoHistory,
        HISTORY_RETENTION_PERIOD = 6 MONTHS
    )
);

在該政策生效後，dbo.WebsiteUserInfoHistory 中的資料列在滿足下列條件時，即符合清理資格：

ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())

你可以在、 WEEKS、 MONTHS或 YEARS中指定保留期DAYS。若省略 HISTORY_RETENTION_PERIOD，保留時間預設為 INFINITE。您也可以明確地使用 INFINITE 關鍵字。

在某些情況下，你可能會想在建立資料表後設定保留值，或更改先前設定的值。在這種情況下，請使用 ALTER TABLE 陳述式：

ALTER TABLE dbo.WebsiteUserInfo
    SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));

Important

將設定從 SYSTEM_VERSIONING 設為 OFF 不會保留保留期間值。將 SYSTEM_VERSIONING 設為 ON 而未明確指定 HISTORY_RETENTION_PERIOD，會保留 INFINITE。

若要檢閱保留原則的目前狀態，請使用下列範例。此查詢聯結資料庫層級的暫時保留啟用旗標與個別資料表的保留期間：

SELECT DB.is_temporal_history_retention_enabled,
       SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
       T1.name AS TemporalTableName,
       SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
       T2.name AS HistoryTableName,
       T1.history_retention_period,
       T1.history_retention_period_unit_desc
FROM sys.tables AS T1
    OUTER APPLY (
        SELECT is_temporal_history_retention_enabled
        FROM sys.databases
        WHERE name = DB_NAME()
) AS DB
    LEFT OUTER JOIN sys.tables AS T2
        ON T1.history_table_id = T2.object_id
WHERE T1.temporal_type = 2;

資料庫引擎如何刪除過時資料列

清理過程取決於歷程記錄資料表的索引配置。你可以只在擁有叢集列儲存（B-tree）或叢集欄位儲存索引的歷史資料表上設定有限保留策略。背景任務負責對所有具有有限保留期的時序表進行過時資料清理。

Note

文件通常會使用「B 型樹狀結構」一詞來指稱索引。在資料列存放區索引中，資料庫引擎會實作 B+ 樹狀結構。這不適用於列存儲索引或記憶體最佳化資料表上的索引。如需詳細資訊，請參閱 SQL Server 和 Azure SQL 索引架構和設計指南。

B-tree 列存索引

資料列存放的叢集索引開頭必須是對應於 SYSTEM_TIME 期間結束的欄位。如果沒有這樣的索引，你就無法設定有限的保留期：

Msg 13765, Level 16, State 1
Setting finite retention period failed on system-versioned temporal table
'dbo.WebsiteUserInfo' because the history table 'dbo.WebsiteUserInfoHistory'
does not contain required clustered index. Consider creating a clustered
columnstore or B-tree index starting with the column that matches end of
SYSTEM_TIME period, on the history table.

預設歷史表已經有一個合規的叢集索引。如果你嘗試將該索引丟棄到具有有限保留期的歷史表中，操作會因以下錯誤而失敗：

Msg 13766, Level 16, State 1
Cannot drop the clustered index 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory'
because it is being used for automatic cleanup of aged data. Consider setting HISTORY_RETENTION_PERIOD to INFINITE on the corresponding system-versioned
temporal table if you need to drop this index.

列倉庫叢集索引的清理邏輯會將舊列以較小區塊刪除（最多可達 10,000 個），以降低對資料庫日誌及 I/O 子系統的壓力。雖然清理邏輯使用了必要的 B 樹索引，但無法保證刪除超過保留期限之資料列的順序。請勿依賴應用程式中的清除順序。

群集列存儲索引

叢集欄位儲存的清理任務會一次移除整個列群組。每個列群組通常包含一百萬列。這種方法效率更高，尤其是在您的工作負載快速產生歷史資料時。

資料壓縮與保留清理使叢集欄位儲存索引成為工作負載快速產生大量歷史資料時的良好選擇。這種模式常見於使用時序表進行變更追蹤與稽核、趨勢分析或物聯網（IoT）資料擷取的密集交易處理工作負載。

當歷史資料列依遞增順序載入時（依期末資料行排序），叢集資料行存放索引的清理作業能達到最佳效果。當只有 SYSTEM_VERSIONING 機制填入歷史表時，此條件總是存在。如果歷史資料表中的資料列未依期間結束欄位排序（在移轉現有的歷史資料時可能會發生這種情況），請在已正確排序的 B-tree 資料列存放索引之上重新建立叢集資料行存放索引，以獲得最佳效能。

避免在具有有限保留期的歷史表上重建叢集欄位儲存索引，因為重建可能會改變系統版本管理操作自然施加的資料列群組排序。如果你需要重建歷史資料表上的叢集欄位儲存索引，請在符合規範的 B 樹索引上重新建立，以保留定期資料清理所需的列組排序。如果你建立一個時序表，裡面有一個已有的歷史表，且欄位儲存索引為叢集且沒有保證資料順序，請採取相同的方法：

/* Create B-tree ordered by the end-of-period column */
CREATE CLUSTERED INDEX IX_WebsiteUserInfoHistory
    ON WebsiteUserInfoHistory(ValidTo) WITH (DROP_EXISTING = ON);
GO

/* Re-create the clustered columnstore index */
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
    ON WebsiteUserInfoHistory WITH (DROP_EXISTING = ON);

當你為具有叢集欄位儲存索引的歷史表設定有限保留期時，你無法在該資料表上建立額外的非叢集 B 樹索引：

CREATE NONCLUSTERED INDEX IX_WebHistNCI
    ON WebsiteUserInfoHistory(UserName);

前述陳述會因以下錯誤而失敗：

Msg 13772, Level 16, State 1
Cannot create non-clustered index on a temporal history table 'WebsiteUserInfoHistory' since it has finite retention period and clustered columnstore index defined.

具有保留原則的查詢數據表

時間表上的所有查詢都會自動過濾出符合有限保留策略的歷史資料列，以避免不可預測且不一致的結果。清理任務會在任何 時間點以任意順序刪除過時的列。

以下截圖展示了基本查詢的查詢計畫。此範例假設表上的WebsiteUserInfo保留期為一：MONTH

SELECT *
FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

查詢計畫在歷史表的叢集索引掃描運算子（下圖中標示）的期末欄位ValidTo（）上包含額外篩選器。

如果你直接查詢歷史表，可能會看到比指定保留期限還要久的列，但無法保證查詢結果會重複出現。以下截圖顯示了歷史資料表查詢的查詢計畫，且未加額外篩選器：

未套用保留篩選條件而直接查詢歷程資料表時的查詢計畫截圖。

不要依賴業務邏輯去讀取保留期以外的歷史表，因為可能會得到不一致或意外的結果。使用含有 FOR SYSTEM_TIME 子句的時間查詢來分析時間資料表中的資料。

時間點還原的考量

當你將資料庫還原到特定時間點時，新的資料庫在資料庫層級會被關閉時間保留（is_temporal_history_retention_enabled 設定為 OFF）。此行為可讓你在清理任務將其移除之前，檢查早於保留期限的歷史資料列。若要恢復對還原資料庫的自動清理，請回到 TEMPORAL_HISTORY_RETENTIONON。

Note

在 Azure SQL Database 進階層中建立的資料庫，備份最多可保留 35 天，因此你可以在該期間內將其還原到任一時間點。對於保留期為一個月的時間表，你可以直接在還原的資料庫查詢歷史表，檢查最多 65 天的歷史資料列。

使用資料表分割

分區的資料表和索引可讓大型資料表更易於管理和擴充。透過資料表分割方法，你可以根據時間條件實作自訂資料清理或離線歸檔。在查詢時使用分割淘汰來處理時間資料表的歷史資料子集時，資料分割也能帶來效能上的提升。

使用表格分割實現滑動視窗，將歷史資料中最舊的部分移出，並保持保留部分大小依年齡不變。滑動視窗會在歷史表中維持等於所需保留期間的資料。歷史表支援在 ON 為 SYSTEM_VERSIONING 時將資料切換出去，這表示你可以清理一部分歷史資料，而不必導入維護視窗，也不會阻斷你的一般工作負載。

Note

要執行分割區切換，歷史表上的叢集索引必須與分割結構對齊（必須包含 ValidTo）。預設的歷史資料表包含包含 ValidTo 和 ValidFrom 欄位的叢集索引，這對於分割、插入新歷史資料及典型的時間查詢來說是最佳選擇。如需相關資訊，請參閱時態表。

滑動視窗需要兩組任務：

分區設定工作
週期性分割區維護任務

舉例來說，假設你想保留六個月的歷史資料，並且每個月的資料都放在獨立的分割區。另外，假設你在 2023 年 9 月啟動了系統版本控制。

分割配置任務會創建歷程記錄資料表的初始分割配置。在這個例子中，你建立的分割區數量與滑動視窗大小相同，以月份計算，加上一個額外的空分割區。此配置確保系統在您開始定期分割區維護任務時，能正確儲存新資料。同時也確保你不會分割包含資料的分割區，避免昂貴的資料移動。以 RANGE LEFT 而非 RANGE RIGHT 來定義分割函數。欲了解更多資訊，請參閱本文後面的「資料表分割時的效能考量」。

下圖顯示了初始分割配置，以保留六個月的資料。

第一與最後兩個分割分別在下邊界與上邊界開放，以確保每個新列都有目的地分割區，無論分割欄位中的值為何。隨著時間推移，歷史表中新增的列會落在較高的分割區。當第六個分區填滿時，你就達到了目標保留期。此時，開始重複進行分割區維護任務。將其排程為定期執行，本例中為每月一次。

下圖展示了重複進行的分割區維護任務。

每次重複維護任務執行以下步驟：

SWITCH OUT建立一個暫存表，然後透過 ALTER TABLE 帶有 SWITCH PARTITION 參數的陳述式在歷史表與暫存表之間切換分割區。
```
ALTER TABLE [<history table>]
    SWITCH PARTITION 1 TO [<staging table>];
```
分割區切換後，你可以選擇性地從暫存表中歸檔資料，然後丟棄或截斷暫存表，以準備下一個維護週期。
MERGE RANGE：使用搭配 ALTER PARTITION FUNCTION 的 2 陳述式，將空白分割區 MERGE RANGE 與分割區 1 合併。當你使用此函數移除最低邊界時，實際上是將空區 1 與先前的區 2 塊合併，形成一個新的區塊 1。其他資料分割也可以有效地變更其序數。
SPLIT RANGE：使用ALTER PARTITION FUNCTION帶有SPLIT RANGE的陳述建立一個新的空分割7。當你用這個函式新增一個上邊界時，實際上就是為下個月建立一個獨立的分割區。

使用 Transact-SQL 在歷程記錄資料表上建立資料分割

請使用以下 Transact-SQL 腳本建立分割函式、分割結構，並重新建立與結構分割對齊的叢集索引。在此範例中，您可以從 2023 年 9 月開始，利用每月劃分來建立一個為期六個月的滑動時間窗口。

BEGIN TRANSACTION;

/*Create partition function*/
CREATE PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo](DATETIME2 (7))
    AS RANGE LEFT FOR VALUES (
        N'2023-09-30T23:59:59.999',
        N'2023-10-31T23:59:59.999',
        N'2023-11-30T23:59:59.999',
        N'2023-12-31T23:59:59.999',
        N'2024-01-31T23:59:59.999',
        N'2024-02-29T23:59:59.999'
    );

/*Create partition scheme*/
CREATE PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo]
    AS PARTITION [fn_Partition_DepartmentHistory_By_ValidTo]
    TO (
        [PRIMARY],
        [PRIMARY],
        [PRIMARY],
        [PRIMARY],
        [PRIMARY],
        [PRIMARY],
        [PRIMARY]
    );

/*Re-create index to be partition-aligned with the partitioning schema*/
CREATE CLUSTERED INDEX [ix_DepartmentHistory] ON [dbo].[DepartmentHistory] (
    ValidTo ASC,
    ValidFrom ASC
)
WITH (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = ON,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    DATA_COMPRESSION = PAGE
)
ON [sch_Partition_DepartmentHistory_By_ValidTo] (ValidTo);

COMMIT TRANSACTION;

使用 Transact-SQL 來維護滑動視窗案例中的資料分割

使用下列 Transact-SQL 指令碼來維護滑動視窗案例中的資料分割。在這個範例中，你會使用 MERGE RANGE 替換 2023 年 9 月的分割區，然後使用 SPLIT RANGE 新增 2024 年 3 月的分割區。

BEGIN TRANSACTION;

/* (1) Create staging table */
CREATE TABLE [dbo].[staging_DepartmentHistory_September_2023]
(
    DeptID INT NOT NULL,
    DeptName VARCHAR (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 (7) NOT NULL,
    ValidTo DATETIME2 (7) NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

/* (2) Create index on the same filegroups as the partition to switch out */
CREATE CLUSTERED INDEX [ix_staging_DepartmentHistory_September_2023]
ON [dbo].[staging_DepartmentHistory_September_2023](
    ValidTo ASC,
    ValidFrom ASC
)
WITH (
    PAD_INDEX = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY];

/* (3) Create constraints matching the partition to switch out */
ALTER TABLE [dbo].[staging_DepartmentHistory_September_2023] WITH CHECK
    ADD CONSTRAINT [chk_staging_DepartmentHistory_September_2023_partition_1]
            CHECK (ValidTo <= N'2023-09-30T23:59:59.999');

ALTER TABLE [dbo].[staging_DepartmentHistory_September_2023]
    CHECK CONSTRAINT [chk_staging_DepartmentHistory_September_2023_partition_1];

/* (4) Switch partition to staging table */
ALTER TABLE [dbo].[DepartmentHistory]
    SWITCH PARTITION 1 TO [dbo].[staging_DepartmentHistory_September_2023]
    WITH (
        WAIT_AT_LOW_PRIORITY (
            MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE
        )
    );

/* (5) [Commented out] Optionally archive the data and drop staging table
      INSERT INTO [ArchiveDB].[dbo].[DepartmentHistory]
      SELECT * FROM [dbo].[staging_DepartmentHistory_September_2023];
      DROP TABLE [dbo].[staging_DepartmentHIstory_September_2023];
*/

/* (6) merge range to move lower boundary one month ahead */
ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
    MERGE RANGE (N'2023-09-30T23:59:59.999');

/* (7) Create new empty partition for "April and after"
by creating new boundary point and specifying NEXT USED file group*/
ALTER PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo]
    NEXT USED [PRIMARY];

ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
    SPLIT RANGE (N'2024-03-31T23:59:59.999');

COMMIT TRANSACTION;

不過，最佳解決方案是每月定期執行一個通用 Transact-SQL 腳本，且不作修改。你可以將前一個腳本加以泛化，使其能根據你提供的參數運作（需要合併的下邊界，以及分割磁碟分割區時建立的新邊界）。為了避免每個月都建立暫存表，建議事先建立一個，並透過修改檢查約束來重用，使其與你切換的分割區相符。欲了解更多資訊，請參閱如何完全自動化滑動視窗情境。

資料表分割效能考量

以避免資料移動的方式執行 SPLIT RANGE 和 MERGE RANGE 作業，因為資料移動可能會造成大量的效能額外負擔。如需詳細資訊，請參閱變更分割函式。

當你將分割函數建立為 RANGE LEFT時，指定的值即為分割的上界。當您使用 RANGE RIGHT 時，指定的值是資料分割的下限。當您使用 MERGE RANGE 操作來移除分區函數定義中的界限時，基礎實作也會移除包含該界限的分區。如果該分割區不是空的，就 MERGE RANGE 把資料移到產生的分割區。

下表描述 RANGE LEFT 和 RANGE RIGHT 選項：

在滑動視窗案例中，請務必移除最低的分區邊界。

RANGE LEFT 情況：最低的分割區邊界屬於分割區 1，而該分割區在分割區切換出之後是空的，因此 MERGE RANGE 不會造成任何資料移動。
RANGE RIGHT 情況：最低的分割邊界屬於劃分 2，而該劃分並非空的，因為切換出去只會清空劃分 1。在此情況下，MERGE RANGE 會造成資料移動，將資料從分割區 2 移動到分割區 1。為避免資料移動，在滑動視窗情境中，RANGE RIGHT 需要具有分割區 1，而該分割區一律為空。這項要求表示，若您使用 RANGE RIGHT，則相較於 RANGE LEFT 的情況，您應該額外建立並維護一個分割區。

結論：使用 RANGE LEFT 滑動分割區管理會更簡單，且避免資料移動。不過，使用 RANGE RIGHT 來定義分割區界限稍微簡單一些，因為您不需要處理日期和時間的對時問題。

使用自訂的清理腳本

當你的資料表沒有保留政策，且資料表分割不可行時，你可以使用自訂的清理腳本刪除歷史資料表中的資料。此過程只有在 SYSTEM_VERSIONING = OFF 時才有可能。為避免資料不一致，應在維護期間（修改資料的工作負載未啟動時）或在交易期間（有效阻擋其他工作負載）進行清理。這項作業需要目前和歷程記錄資料表的 CONTROL 權限。

清理邏輯對每個時序表都是一樣的，所以你可以用通用的儲存程序自動化。使用 SQL Server Agent 或其他工具排程該程序，每天執行，並遍歷每個想要限制資料歷史的時態表。

以下圖示說明如何組織單一資料表的清理邏輯，以減少對執行工作負載的影響。

以下是實施此流程的一些高層次指引：

將每個時序表中的歷史資料分成多次小區塊，刪除它們。從最舊的排開始，然後往最近的一排開始。避免如前圖所示，在單一交易中刪除所有資料列。雖然沒有單一區塊大小適用於所有情境，但單一交易刪除超過 10,000 筆資料可能會帶來重大懲罰。
每次迭代都實作為通用儲存程序的呼叫，從歷史資料表中移除部分資料。
在每次叫用程序時，計算需要刪除單一時態表中多少資料列。根據結果和你想要的迭代次數，為每個程序調用決定動態分割點。
針對單一資料表，規劃每次迭代之間的延遲，以降低對存取時態資料表之應用程式的影響。

以下的儲存程序會刪除單一時序資料表的資料。它會從目錄檢視中發現歷史表和週期結束欄位，然後在交易中執行三個語句：SET SYSTEM_VERSIONING = OFF、、 DELETE FROM <history_table>SET SYSTEM_VERSIONING = ON和。請仔細審查這些程式碼，並在應用於你的環境中前進行調整。

在 SQL Server 2016 (13.x) 中，前兩個步驟必須在個別 EXECUTE 陳述式中執行，否則 SQL Server 會產生類似下列範例的錯誤：

Msg 13560, Level 16, State 1, Line XXX
Cannot delete rows from a temporal history table '<database_name>.<history_table_schema_name>.<history_table_name>'.

DROP PROCEDURE IF EXISTS usp_CleanupHistoryData;
GO

CREATE PROCEDURE usp_CleanupHistoryData (
    @temporalTableSchema SYSNAME,
    @temporalTableName SYSNAME,
    @cleanupOlderThanDate DATETIME2
)
AS
DECLARE @disableVersioningScript AS NVARCHAR (MAX) = '';
DECLARE @deleteHistoryDataScript AS NVARCHAR (MAX) = '';
DECLARE @enableVersioningScript AS NVARCHAR (MAX) = '';
DECLARE @historyTableName AS SYSNAME;
DECLARE @historyTableSchema AS SYSNAME;
DECLARE @periodColumnName AS SYSNAME;

/* Generate script to discover history table name and
end of period column for given temporal table name */
EXECUTE sp_executesql N'
    SELECT @hst_tbl_nm = t2.name,
           @hst_sch_nm = s2.name,
           @period_col_nm = c.name
    FROM sys.tables AS t1
         INNER JOIN sys.tables AS t2
             ON t1.history_table_id = t2.object_id
         INNER JOIN sys.schemas AS s1
             ON t1.schema_id = s1.schema_id
         INNER JOIN sys.schemas AS s2
             ON t2.schema_id = s2.schema_id
         INNER JOIN sys.periods AS p
             ON p.object_id = t1.object_id
         INNER JOIN sys.columns AS c
             ON p.end_column_id = c.column_id
            AND c.object_id = t1.object_id
    WHERE t1.name = @tblName
          AND s1.name = @schName',
    N'@tblName sysname,
    @schName sysname,
    @hst_tbl_nm sysname OUTPUT,
    @hst_sch_nm sysname OUTPUT,
    @period_col_nm sysname OUTPUT',
@tblName = @temporalTableName,
@schName = @temporalTableSchema,
@hst_tbl_nm = @historyTableName OUTPUT,
@hst_sch_nm = @historyTableSchema OUTPUT,
@period_col_nm = @periodColumnName OUTPUT;

IF @historyTableName IS NULL
   OR @historyTableSchema IS NULL
   OR @periodColumnName IS NULL
    THROW 50010, 'History table cannot be found. Either specified table is not system-versioned temporal or you have provided incorrect argument values.', 1;

SET @disableVersioningScript = @disableVersioningScript +
    'ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + ']
    SET (SYSTEM_VERSIONING = OFF)';

SET @deleteHistoryDataScript = @deleteHistoryDataScript +
    ' DELETE FROM [' + @historyTableSchema + '].[' + @historyTableName + ']
    WHERE [' + @periodColumnName + '] < ' + '''' +
    CONVERT (VARCHAR (128), @cleanupOlderThanDate, 126) + '''';

SET @enableVersioningScript = @enableVersioningScript +
    ' ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + ']
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[' +
    @historyTableName + '], DATA_CONSISTENCY_CHECK = OFF )); ';

BEGIN TRANSACTION;
    EXECUTE (@disableVersioningScript);
    EXECUTE (@deleteHistoryDataScript);
    EXECUTE (@enableVersioningScript);
COMMIT TRANSACTION;

意見反應

此頁面對您有幫助嗎？

Last updated on 2026-08-19

管理系統版本化時間表中的歷史數據保留

規劃資料保留政策

使用時間歷史保留政策

如何設定保留原則？

資料庫引擎如何刪除過時資料列

B-tree 列存索引

群集列存儲索引

具有保留原則的查詢數據表

時間點還原的考量

使用資料表分割

使用 Transact-SQL 在歷程記錄資料表上建立資料分割

使用 Transact-SQL 來維護滑動視窗案例中的資料分割

資料表分割效能考量

使用自訂的清理腳本

相關內容

意見反應

其他資源