システム バージョン管理されたテンポラル テーブルの履歴データの保有期間管理

対象者:SQL Server 2016 (13.x) およびそれ以降のバージョン Azure SQL DatabaseAzure SQL Managed InstanceSQL Database in Microsoft Fabric

システムバージョン対応の時間テーブルは、すべての行の過去バージョンを履歴テーブルに保持します。 履歴テーブルは通常のテーブルよりもデータベースサイズを以下の場合に増加させることがあります:

• あなたは歴史的データを長期間保持します。
• 更新や削除の多いデータ変更パターンがあります。

大規模で常に拡大する履歴テーブルは、ストレージコストや時間クエリに課されるパフォーマンスタイントの両面で問題になる可能性があります。 履歴テーブルのデータ保持ポリシーを策定することは、すべての時間テーブルのライフサイクルを計画・管理する上で重要な要素です。

データ保持ポリシーを計画しましょう

時間テーブルデータの保持を管理するには、まず各時間テーブルの必要な保持期間を決定します。 ほとんどの場合、保持ポリシーは時間テーブルを使用するアプリケーションのビジネスロジックの一部であるべきです。 例えば、データ監査やタイムトラベルのシナリオでの応用では、オンラインクエリのために過去のデータがどれくらいの期間利用可能でなければならないかという明確な要件があります。

データの保持期間を決めたら、過去のデータ管理計画を立てましょう。 履歴データを格納する方法と場所、および保持要件より古い履歴データを削除する方法を決めるます。

この記事のすべてのアプローチは、現在の表の周期末に対応する列、すなわち以下の例の ValidTo 列に作用します。 各行の期間終了値により、その行のバージョンが 閉じられる、つまり、履歴テーブルに入るタイミングが決定されます。 例えば、状態 ValidTo < DATEADD (DAY, -30, SYSUTCDATETIME()) 30日以上前の過去のデータと一致します。

それらの行を処理するには、次のいずれかの方法を選択します:

この記事の分割およびカスタムクリーンアップの例は、「 システムバージョン付き時時テーブルの作成」 記事のサンプルを使用しています。

時間履歴保持ポリシーを活用してください

対象:SQL Server 2017(14.x)以降のバージョン、Azure SQL Database、Azure SQL Managed Instance、Microsoft FabricのSQL database。

個々のテーブルレベルで時間履歴保持を設定でき、柔軟なエイジングポリシーを作成できます。 時間的保持を可能にするには、テーブル作成やスキーマ変更時に HISTORY_RETENTION_PERIOD を設定します。

保持ポリシーを定義した後、データベース エンジンはスケジュールされたバックグラウンドタスクを実行し、保持期間より古い期間の値を持つ過去の行を見つけて透過的に削除します。

リテンション ポリシーの構成方法

テンポラル テーブルの保持期間ポリシーを構成する前に、データベース レベルでテンポラル履歴保持期間が有効になっているかどうかを確認します。

SELECT is_temporal_history_retention_enabled,
       name
FROM sys.databases;

データベースフラグはデフォルトis_temporal_history_retention_enabledONですが、ALTER DATABASE文を使って変更できます。 また、データベース エンジンはポイントインタイム復元(PITR)操作後に自動的にOFFする設定もしており、これは「ポイントインタイム復元の考慮事項」で説明されています。 データベースのテンポラル履歴保持期間のクリーンアップを有効にするには、次のステートメントを実行します。 <myDB>を変更したいデータベースに置き換えてください:

ALTER DATABASE [<myDB>]
    SET TEMPORAL_HISTORY_RETENTION ON;

テーブル作成時に 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())

保持期間は DAYS、 WEEKS、 MONTHS、または YEARSで指定できます。 HISTORY_RETENTION_PERIODを省くと、保持はデフォルトで INFINITE になります。 INFINITE キーワードを明示的に使うこともできます。

場合によっては、テーブル作成後に保持設定をしたり、以前設定した値を変更したりしたい場合もあります。 その場合は、次の ALTER TABLE ステートメントを使用します。

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

保持ポリシーの現在の状態を確認するには、次のサンプルを使用します。 このクエリは、データベース レベルのテンポラル保持期間有効化フラグと個々のテーブルの保持期間を結合します。

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ツリー)またはクラスタ化されたカラムストアインデックスを持つ履歴テーブルでのみ設定できます。 バックグラウンドタスクは、保持期間が有限のすべての時間テーブルに対して古いデータのクリーンアップを行います。

B ツリーの行ストア インデックス

行ストアのクラスタインデックスは、 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.

rowstoreのクラスタインデックスのクリーンアップロジックは、古い行をより小さなチャンク(最大10,000個まで)に分けて削除し、データベースログやI/Oサブシステムへの負荷を最小限に抑えます。 クリーンアップロジックは必要なBツリーインデックスを使用しますが、保持期間より古い行の削除順序を保証することはできません。 アプリケーションではクリーンアップ順序に依存しないでください。

クラスター化列ストア インデックス

クラスタ化されたカラムストアのクリーンアップタスクは、一度に 行グループ 全体を削除するものです。 各行グループは通常100万行を含みます。 この方法は、特にワークロードが高速で過去データを生成する場合、より効率的です。

データ圧縮と保持のクリーンアップにより、クラスタ化されたコラムストアインデックスは、ワークロードが大量の過去データを急速に生成するシナリオに適した選択肢となります。 このパターンは、変更追跡や監査、トレンド分析、IoT(モノのインターネット)データ取り込みに時間 テーブルを使用する集中的なトランザクション処理ワークロード に典型的です。

クラスタ化列ストア インデックスのクリーンアップは、履歴行が昇順（期間終了列で並べ替え）で取り込まれるときに最も効率よく機能します。 この条件は、 SYSTEM_VERSIONING メカニズムのみが履歴テーブルを埋める場合に常に当てはまります。 履歴テーブルの行が期間終了列で並んでいない場合(既存の過去データを移行する際に起こりうる)、最適なパフォーマンスを得るために、適切に順序付けられたBツリーの行ストアインデックスの上にクラスタ化されたカラムストアインデックスを再作成してください。

保存期間が有限の履歴テーブル上のクラスタ化されたカラムストアインデックスの再構築は避けてください。なぜなら、再構築するとシステムバージョン管理操作が自然に課す行群の順序が変わる可能性があるからです。 履歴テーブルのクラスタ化されたカラムストアインデックスを再構築する必要がある場合は、定期的なデータクリーンアップに必要な行グループの順序を保つために、準拠した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テーブルに1MONTHの保持期間があると仮定しています:

SELECT *
FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

クエリプランには、履歴テーブルのクラスタインデックススキャン演算子(以下の画像で強調)の期間末尾列(ValidTo)に追加のフィルターが含まれています。

履歴テーブルを直接照会すると、指定された保持期間より古い行が見られますが、繰り返し可能なクエリ結果を保証することはありません。 以下のスクリーンショットは、追加のフィルターなしの履歴テーブル上のクエリのクエリ計画を示しています:

保持期間を超えて履歴表を読み取るビジネスロジックに頼らないでください。そうすると一貫性がなかったり予期しない結果が出る可能性があります。 時間テーブル内のデータを分析するには、 FOR SYSTEM_TIME 節を使った時間クエリを用いましょう。

ポイントインタイム リストアの考慮事項

データベースを特定の時点に復元すると、新しいデータベースはデータベースレベルで時間保持が無効になります(is_temporal_history_retention_enabledOFFに設定されています)。 この動作により、保持期間より古い過去の行をクリーンアップタスクで削除する前に確認できます。 復元されたデータベースの自動クリーンアップを再開するには、 TEMPORAL_HISTORY_RETENTION を ONに戻してください。

テーブルのパーティショニングを使用

パーティション分割とインデックス により、大規模なテーブルがより管理しやすくなり、拡張性が向上します。 テーブル分割方式を用いることで、時間条件に基づくカスタムデータのクリーンアップやオフラインアーカイブを実装できます。 テーブル パーティション分割でも、パーティションの削除を利用してデータ履歴のサブセットのテンポラル テーブルにクエリを実行するとき、パフォーマンス上の利点が得られます。

テーブル分割を用いて、履歴テーブルから最も古い部分を移動させるスライディングウィンドウを実装し、保持された部分のサイズは年齢によって一定に保ちます。 スライディングウィンドウは、必要な保持期間に等しいデータを履歴テーブルに保持します。 履歴テーブルは SYSTEM_VERSIONING が ON稼働中でもデータを切り替えることをサポートしており、メンテナンスウィンドウを導入したり通常の作業負荷を妨げたりせずに履歴データの一部をクリーンアップできます。

スライディングウィンドウには、2つのタスクセットが必要です:

• パーティション分割構成タスク
• 定期的なパーティション保守タスク

この例では、過去6か月間データを保持し、毎月のデータを別々のパーティションに保存したいと仮定します。 また、2023年9月にシステムバージョン設定を有効にしたと仮定してください。

パーティション分割構成タスクでは、履歴テーブルの初回パーティション分割構成を作成します。 この例では、スライディングウィンドウのサイズと同じ数のパーティションを数ヶ月単位で作成し、さらに空のパーティションを1つ追加します。 この構成により、定期的にパーティションのメンテナンス作業を始めた際に新しいデータを正しく保存できます。 また、データを含むパーティションを分割しないようにし、高価なデータ移動を回避できます。 分割関数をRANGE RIGHTではなくRANGE LEFTで定義します。 詳細については、この記事の後半にある 「テーブル分割に関するパフォーマンスの考慮 事項」をご覧ください。

以下の図は、6か月分のデータを保持するための初期のパーティショニング設定を示しています。

最初のパーティションと最後のパーティションはそれぞれ下境界と上限で 開かれ ており、分割列の値に関係なく、すべての新しい行に宛先パーティションが存在するようにしています。 時間が経つにつれて、履歴テーブルの新しい行はより高いパーティションに格納されます。 6番目のパーティションが満杯になると、目標保持期間に到達します。 ここで、初めて定期的なパーティションメンテナンス作業を開始します。 この例では月に一度、定期的に実行するようにスケジュールしてください。

以下の図は、定期的なパーティションメンテナンス作業を示しています。

定期的なメンテナンス作業の各実行は以下のステップを実行します。

1. SWITCH OUTステージングテーブルを作成し、 ALTER TABLE 文と SWITCH PARTITION 引数を使って、ヒストリーテーブルとステージングテーブル間のパーティションを切り替えます。

   ALTER TABLE [<history table>]
       SWITCH PARTITION 1 TO [<staging table>];
   

   パーティションスイッチの後、ステージングテーブルからデータをアーカイブし、次のメンテナンスサイクルに備えてステージングテーブルを削除または切り捨てることも可能です。

2. MERGE RANGE空のパーティション1をパーティション2とMERGE RANGEのALTER PARTITION FUNCTION文を用いてマージします。 この関数を使って最低境界を除去すると、空のパーティション 1 と以前のパーティション 2 を実質的に統合し、新しいパーティション 1を形成します。 結果として、他のパーティションの序数も変更されます。

3. SPLIT RANGE: 7付きのSPLIT RANGEステートメントを使用して、新しい空のパーティションALTER PARTITION FUNCTIONを作成します。 この関数を使って新しい上限を追加すると、実質的に次の月のための別のパーティションが作成されます。

Transact-SQL を利用し、履歴テーブルでパーティションを作成する

以下の Transact-SQL スクリプトを使ってパーティション関数、パーティションスキーマを作成し、スキーマにパーティションアライメントするクラスタインデックスを再作成します。 この例では、月単位のパーティションで 2023 年 9 月に開始する 6 か月のスライディング ウィンドウを作成します。

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 スクリプトを利用し、スライディング ウィンドウ シナリオのパーティションを維持します。 この例では、2023年9月のパーティションを MERGE RANGEで切り替え、2024年3月のパーティションを SPLIT RANGEで新たに追加します。

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 スクリプトを定期的に実行することです。 前のスクリプトを一般化して、提供したパラメータ(マージすべき下限と分割によって作られる新しい境界)に作用させることができます。 毎月ステージングテーブルを作成するのを避けるために、事前に作成しておき、切り替えるパーティションに合わせてチェック制約を変更して再利用してください。詳細は、スライド ウィンドウシナリオの完全自動化方法をご覧ください。

テーブル パーティション分割に関するパフォーマンス上の考慮事項

データ移動を避ける方法で MERGE RANGE および SPLIT 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 ケースと比べて1つ余分にパーティションを作成・維持する必要があることを意味します。

結論:スライド式パーティションで RANGE LEFT を使うことでパーティション管理が容易になり、データの移動を回避できます。 RANGE RIGHT でパーティション境界を定義する場合、日時のチェック問題を扱う必要がないため、少し単になります。

カスタムクリーンアップスクリプトを使いましょう

保持ポリシーがテーブルにない場合やテーブルのパーティショニングが現実的でない場合、カスタムのクリーンアップスクリプトを使って履歴テーブルからデータを削除できます。 このプロセスは SYSTEM_VERSIONING = OFFがない場合のみ可能です。 データの不整合を避けるために、メンテナンスウィンドウ(データを修正するワークロードがアクティブでない時)やトランザクション中(他のワークロードを効果的にブロックする)にクリーンアップを行います。 この操作には、現行テーブルと履歴テーブルの CONTROL 権限が必要になります。

クリーンアップロジックはすべての時間テーブルで同じなので、汎用ストアドプロシージャで自動化できます。 SQL Server エージェントや他のツールを使って、そのプロシージャを毎日実行するようにスケジュールし、データ履歴を制限したいすべての時間テーブルを反復処理します。

以下の図は、実行中のワークロードへの影響を減らすために、クリーンアップロジックを単一のテーブルで整理する方法を示しています。

以下は、このプロセスを実施するための大まかなガイドラインです。

• すべての時間テーブルの過去データを、小さなチャンクに分けて何度も繰り返し削除します。 最も古い行から始めて、最も新しい行へと進んでいきます。 前の図が示すように、単一のトランザクションですべての行を削除するのは避けてください。 単一のチャンクサイズがすべてのシナリオに適用できるわけではありませんが、1回のトランザクションで10,000行以上を削除すると大きなペナルティが発生する可能性があります。

• すべての反復を汎用ストアドプロシージャの呼び出しとして実装し、履歴テーブルからデータの一部を除去します。

• プロセスを呼び出すたびに、個々のテンポラル テーブルに対して、削除する行を計算します。 結果と望む反復回数に基づいて、各プロシージャ呼び出しに対して動的分割点を決定します。

• 単一のテーブルに対して反復間の遅延を計画し、時間テーブルにアクセスするアプリケーションへの影響を軽減します。

以下のストアドプロシージャは、単一の時間テーブルのデータを削除します。 カタログビューから履歴テーブルと期間終了列を発見し、トランザクション内で3つの文を実行します: SET SYSTEM_VERSIONING = OFF、 DELETE FROM <history_table>、 SET SYSTEM_VERSIONING = ON。 このコードをよく見直し、環境に適用する前に調整してください。

SQL Server 2016 (13.x) では、最初の 2 つの手順を個別の 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;

関連するコンテンツ

• テンポラル テーブル
• システム バージョン管理テンポラル テーブルの使用を開始する
• テンポラル テーブルのシステム一貫性のチェック
• テンポラル テーブルでのパーティション分割
• テンポラル テーブルの考慮事項と制約
• テンポラル テーブルのセキュリティ
• メモリ最適化テーブルを用いた、システム バージョン管理された一時テーブル
• テンポラル テーブル メタデータのビューおよび関数