2016 に追加された Temporal Table ですが、2017 では Retention Policy が追加されていました。
2016 で追加された時に、不要になった過去データはどうするかなー?っと思ってたのですが、2017 で履歴の保有期間を設定出来るようになり便利になりますね!
指定出来る単位は、
- DAYS
- WEEKS
- MONTHS
- YEARS
alter table [tableName] set ( system_versiong = on ( history_retention_period = 1 months ) )
みたいな感じで Temporal Table を設定する際に期間を指定します。
注意点は、履歴テーブルに 期間の終了に対応する列 (SysEndTime 等) から始まる クラスター化インデックスが必要です。
create table ... with (system_versioning=on) 等で、History テーブルを自動で作成した場合は、自動的にクラスター化インデックス(SysEndTime, SysStartTime)が作成されています。
クラスター化インデックスが無い場合に、history_rentension_period を指定すると、以下のエラーになります。
メッセージ 13765、レベル 16、状態 1、行 2 履歴テーブル 'TemporalTableTest.dbo.社員マスタHistory' に必須のクラスター化インデックスがないため、システムでバージョン管理されたテンポラル テーブル 'TemporalTableTest.dbo.社員マスタ' で有限の保有期間を設定できませんでした。SYSTEM_TIME 期間の終わりに一致する列から始まるクラスター化列ストア インデックスまたは B-Tree インデックスを履歴テーブルに作成することをご検討ください。
Temporal Table の Rentention Policy は 以下のクエリで確認する事が出来ます。
select name , temporal_type_desc , history_retention_period , history_retention_period_unit , history_retention_period_unit_desc from sys.tables
sys.tables (Transact-SQL) | Microsoft Docs
データベースにも Rentention Policy に関するフラグを持っているので、そちらも確認しておきましょう。
こちらのフラグは、期限切れのデータを削除するかどうかのフラグになります。
select name , is_temporal_history_retention_enabled from sys.databases
sys.databases (Transact-SQL) | Microsoft Docs
設定を変えるには、こちらのクエリで。
-- ON にする alter database [databaseName] set temporal_history_retention ON; -- OFF にする alter database [databaseName] set temporal_history_retention OFF;
Retention Policy を設定しても、データベースの is_temporal_history_retention_enabled が OFF だとデータの削除は動かないようです。
またデータの削除はバックグラウンドで実行されるようで、削除されるデータの順番は不定のようです。
では、期限が切れたデータがまだテーブルに残っている状態で、クエリを実行したらどうなるのか確認してみます。
まず、History データが削除されないように、DB の is_temporal_history_retention_enabled を OFF にします。
alter database TemporalTableTest set temporal_history_retension OFF;
次に事前準備でテーブルとデータを作成します。
History データも仕込んだ Temporal Table の作り方は、
テストデータを仕込んだ Temporal Tables を作成する - お だ のスペース
に書いていたのでそのまま使います。
変った点は History テーブルにクラスター化インデックスを作成するのと、Rentention Policy を指定するとこですね。
create table [社員マスタ] ( [Id] int not null primary key, [社員名] nvarchar(10) not null, [旧姓] nvarchar(10), [住所] nvarchar(10), [SysStart] datetime2 not null, [SysEnd] datetime2 not null ) create table [社員マスタHistory] ( [Id] int not null, [社員名] nvarchar(10) not null, [旧姓] nvarchar(10), [住所] nvarchar(10), [SysStart] datetime2 not null, [SysEnd] datetime2 not null ) -- データの追加 insert into [社員マスタ] values (1, N'田中 一郎', null, N'大阪府', '2015-04-01 9:00:00', '9999-12-31 23:59:59.9999999'), (2, N'鈴木 花子', null, N'兵庫県', '2015-08-15 15:32:24', '9999-12-31 23:59:59.9999999'), (3, N'伊藤 ゆかり', N'田所', N'奈良県', '2016-01-01 12:10:00', '9999-12-31 23:59:59.9999999'), (4, N'佐藤 次郎', null, N'京都府', '2015-09-01 9:00:00', '9999-12-31 23:59:59.9999999') insert into [社員マスタHistory] values (2, N'一之瀬 花子', N'鈴木', N'兵庫県', '2013-02-23 13:34:33', '2015-08-15 15:32:24'), (3, N'田所 ゆかり', null, N'大阪府', '2014-10-14 20:12:22', '2016-01-01 12:10:00'), (5, N'池田 三郎', null, N'大阪府', '2013-05-17 9:00:00','2015-12-28 17:00:00'), (4, N'佐藤 次郎', null, N'兵庫県', '2012-04-01 9:00:00', '2015-09-01 9:00:00'), (5, N'池田 三郎', null, N'東京都', '2011-04-01 9:00:00', '2013-05-17 9:00:00'), (2, N'鈴木 花子', null, N'兵庫県', '2010-07-01 9:00:00', '2013-02-23 13:34:33') alter table [社員マスタ] add period for system_time ([SysStart], [SysEnd]) create clustered index [IX_社員マスタHistory] on [社員マスタHistory] ([SysEnd], [SysStart]) alter table [社員マスタ] set (system_versioning = on (history_table = [dbo].[社員マスタHistory], history_retention_period = 4 years))
今回は4年前のデータは期限切れにします。
この状態で History テーブルをそのまま select すると…
期限切れている 2013年のデータも表示されています、まだ期限切れのデータが削除されていない状態ですね。
では、Temporal Table の機能を使ってクエリを書いてみると…
期限切れのデータが表示されていません!
for system_time all
を使うと、本来全てのデータを表示するのですが Rentention Policy を設定すると期限切れのデータは出ていません。
この仕組みは実行プランを見ると分かります。
単に History テーブルを select したやつ
次に、Temporal Table の機能で select したやつ
History テーブルに Rentention Policy で指定した値が条件に追加されていますね!
こういう仕組みで Rentention Policy を実現しているようでした。
今回のお話しのドキュメントはこちら
Manage Retention of Historical Data in System-Versioned Temporal Tables | Microsoft Docs
Manage historical data in Temporal Tables with retention policy | Microsoft Docs
日本語が良い方は、en-us を ja-jp にして下さい。