Share via

SQL Server 2016 新機能 Temporal Tables ( テンポラル テーブル ) について - パート1 -

  • 10/13/2016

Microsoft Japan Data Platform Tech Sales Team

中川

本ブログにて、SQL Server 2016 のいくつかの新機能についてご紹介しておりますが、今回は Temporal Tables ( テンポラル テーブル ) という機能について概要や使い方をご紹介いたします。

これまでは、テーブルにDMLが実行された際に変更前のデータを履歴情報として残しておきたい場合には、トリガーやアプリケーション側でハンドリングし、別に用意した履歴テーブルに格納するといった実装を行う必要がありましたが、Temporal Tables はその履歴管理を自動で行ってくれる機能です。また、過去のある時点で有効であったデータ、過去のある期間にデータがどのように変更されていったかなどのデータの変更追跡なども行うこともできるようになっています。

image

 

それでは早速、使い方、および動作を見ていきましょう。今回は非常にシンプルな以下のようなテーブルを用意しました。

image

先ず、Temporal Tables を有効化、つまり履歴管理を有効化したいテーブルには Primary Key が必要になります。また、データの有効期間を管理するために二つの datatime2 型の列( PERIOD 列 )も必要になりますので追加します。

 -- PK を追加 ALTER TABLE Products ADD CONSTRAINT pk1 PRIMARY KEY(商品コード);GO -- 日時を格納するための列を2つ追加ALTER TABLE Products ADD   sysstart datetime2 GENERATED ALWAYS AS ROW START HIDDEN DEFAULT CONVERT(datetime2, '2016-01-01'),   sysend datetime2 GENERATED ALWAYS AS ROW END HIDDEN DEFAULT CONVERT(datetime2, '9999-12-31 23:59:59.9999999'),   PERIOD FOR SYSTEM_TIME ( sysstart, sysend );GO

 

これで、Products 表にて Temporal Tables を有効化する準備は整いましたので有効化します。なお、以下の HISTORY_TABLE 部分には履歴テーブルの名前を指定します。履歴テーブルの名前を指定しない場合には自動で名前が付けられます。

 ALTER TABLE Products SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.ProductsHistory ) ); GO

すると以下のように Products テーブルにて Temporarl Tables が有効化されると同時に、履歴テーブルとなる ProductsHistory テーブルも自動で作成されます。

履歴テーブルとなる ProductsHistory には、データの有効期間を管理するために追加した二つの datatime2 型の列に対するクラスタ化インデックスも自動で作成されています。

image

また、履歴テーブルがページ圧縮の要件を満たしている場合には容量削減のために自動的にページ圧縮も有効になります。ページ圧縮の要件等については 「データ圧縮」 をご参照ください。

image

但し、履歴テーブルに対し、主キー、外部キー、一意なインデックス、テーブル制約、トリガーを作成することはできません。 また、変更データ キャプチャ、変更追跡、トランザクション レプリケーション、またはマージ レプリケーション用に構成することもできません。

今回は事前に履歴テーブルを作成せずに、Temporal Tables を有効化する際に自動で履歴テーブルが作成されていることを確認しましたが、Product テーブルと同じ構造を持つテーブルをあらかじめ用意し、その既存表を履歴テーブルとして使用することも可能です。

 

因みに、Temporal Tables を無効化した際、履歴テーブルは削除されることなく通常テーブルとして保持されたままとまります。

 ALTER TABLE Products SET ( SYSTEM_VERSIONING = OFF )

image

 

では、実際にデータの更新を行ってみます。

先ず、現時点での Products テーブルの状態は以下のようになっています。

 SELECT 商品コード,商品名,sysstart,sysend FROM Products WHERE 商品コード = 1 OR 商品コード = 2

image

この状態でデータを更新してみます。

 -- データの更新 UPDATE Products SET 商品名 = 'AAA_1st' WHERE 商品コード = 1; UPDATE Products SET 商品名 = 'BBB_1st' WHERE 商品コード = 2; GO

すると Products テーブル、および ProductsHistory テーブルは以下のような状態となります。

[Products テーブル]

image

[ProductsHistory テーブル]

image

つまり、現時刻が sysstart 列と sysend 列の間であるデータが現時点で有効なデータであり、sysend 列が現時点より過去のものが履歴データとして履歴テーブルにコピーされるという仕組みです。

では、例えば ”2016-04-01” 時点で有効だったデータを確認してみましょう。

 SELECT 商品コード,商品名,sysstart,sysend FROM Products   FOR SYSTEM_TIME AS OF '2016-04-01' WHERE 商品コード = 1 OR 商品コード = 2 ORDER BY 商品コード; GO

すると”2016-10-03”に更新されたデータではなく、更新前のデータが返ってきます。”2016-04-01” 時点で有効だったデータは”2016-10-03”に更新する前のデータだからです。

image

なお、上記クエリ実行時の実行プランを見ると、Products テーブル、および ProductsHistory テーブルに対して検索が行われていることが分かります。

image

image image

 

次に、'2016-01-01' から '2016-12-31' の間に有効であったデータを確認してみましょう。

 SELECT 商品コード,商品名,sysstart,sysend FROM Products   FOR SYSTEM_TIME FROM '2016-01-01' TO '2016-12-31' WHERE 商品コード = 1 OR 商品コード = 2 ORDER BY 商品コード,sysstart; GO

image

同じく、実行プランを確認すると以下のようになっています。

image

image image

テンポラル クエリを実行した際には、履歴テーブルに対しデータの有効期間を管理するために二つの datatime2 型の列に条件付きでクエリが実行されるため、履歴テーブルにはクラスタ化インデックスを作成することにより検索性能を上げています。

 

上記のように、Temporal Tables を有効化しておくことにより、これまでのようにトリガーなどで実装しなくても履歴管理を行うことができ、またオペレーションミスによるデータの削除などを行った際にもテンポラル クエリを使用してデータを元に戻すこともできますし、データの変更遷移をたどることも可能となります。

今回は概要編としまして Temporal Tables の概要をご紹介しました。次回は設計時や運用時の考慮ポイントをお伝えする予定です。

 

関連記事