おだのスペース

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

f:id:odashinsuke:20180725144418j:plain
sys.tables (Transact-SQL) | Microsoft Docs

データベースにも Rentention Policy に関するフラグを持っているので、そちらも確認しておきましょう。
こちらのフラグは、期限切れのデータを削除するかどうかのフラグになります。

select 
  name
  , is_temporal_history_retention_enabled 
from sys.databases

f:id:odashinsuke:20180725144701j:plain 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 すると… f:id:odashinsuke:20180725150344j:plain
期限切れている 2013年のデータも表示されています、まだ期限切れのデータが削除されていない状態ですね。

では、Temporal Table の機能を使ってクエリを書いてみると… f:id:odashinsuke:20180725150157j:plain
期限切れのデータが表示されていません！
for system_time all を使うと、本来全てのデータを表示するのですが Rentention Policy を設定すると期限切れのデータは出ていません。

この仕組みは実行プランを見ると分かります。
単に History テーブルを select したやつ f:id:odashinsuke:20180725150517j:plain
次に、Temporal Table の機能で select したやつ
f:id:odashinsuke:20180725150315j:plain
History テーブルに Rentention Policy で指定した値が条件に追加されていますね！
こういう仕組みで Rentention Policy を実現しているようでした。