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

Article
10/17/2016

Microsoft Japan Data Platform Tech Sales Team

前回の投稿では、SQL Server 2016 新機能の一つである Temporal Tables の概要をお伝えするとともに簡単な動作についてもご紹介いたしました。今回は、設計時や開発時、運用時に知っておいていただきたいポイントをお伝えします。

1. データの変更について

1.1 データの挿入

前回の投稿では、Temporal Tables を有効化するために datatime2 型の列を二つ ( PERIOD 列 ) 追加しましたが、その際に HIDDEN オプションを指定していました。

HIDDEN を指定していなくても、以下のように列をきちんと指定した上で Insert を行う際には問題はありません。

 INSERT INTO products ([商品コード],[商品名]) VALUES (200,'商品名1');

しかし、列を指定せずに以下のように Insert を実行した場合にはエラーとなります。

 INSERT INTO products VALUES (200,'商品名1');

この場合には、VALUES の PERIOD 列に該当する箇所には default を指定する必要があります。

 INSERT INTO products VALUES (200,'商品名1',default,default);

本来は PERIOD 列はユーザーやアプリケーションが意識する必要のない列ですので、HIDDEN を指定しておくのがよいかと思います。

また、データを大量に登録する必要がある場合などはパーティションを使って一旦ステージングにデータを投入し、パーティションで Switch In させることも可能です。なお、履歴テーブルと整合性を保つため、 Temporal Tables を有効化したテーブルから Switch Out することはできません。

1.2 データの更新

Temporal Tables が有効化されたテーブルの PERIOD 列は更新できません。

 UPDATE [TESTDB01].[dbo].[Products]  SET sysstart='2016-10-08'  WHERE 商品コード = 1

また、履歴テーブルのデータを変更することもできません。

 UPDATE [ProductsHistory]  SET  [商品名] = 'AAA_4th'  WHERE [商品コード] = 1;

これにより、データの整合性を失うようなオペレーションを防ぐことができます。

1.3 データの削除

Temporal Tables が有効化されたテーブル、および履歴テーブルに対し Truncate はできません。データの削除は Delete を使用します。

truncate table products;

truncate table productshistory;

ただ、履歴テーブルの肥大化などにより履歴テーブルのデータを削除する必要がある場合などには、一旦 Temporal Tables を無効化して履歴テーブルに Truncate を実行し、再度 Temporal Tables を有効化することによりデータをパージすることができます。但し、この方法では一時的に Temporal Tables を無効化しますので、その間にデータが変更されないようにするなどの工夫が必要です。

Temporal Tables を無効化することなく保持期間を過ぎた履歴データのみアーカイブしたいといった場合には、パーティションを組み合わせる方法と Stretch Database を組み合わせる方法の二つがあります。パーティション使う場合には、通常のパーティション運用と同じように Switch Out することができます。なお、Temporal Tables を有効化したテーブルとの整合性を保つため、履歴テーブルに対して Switch In はできません。Stretch Database については改めて別の機会に詳しくご紹介いたしますが、SQL Server のテーブル内のデータの一部、あるいは全部を Azure 上に自動的にアーカイブする機能です。履歴テーブルに対して Stretch Database を有効化することにより自動的に Azure にデータがアーカイブされていくため、運用の手間を減らすことができます。

2. スキーマの変更について

Temporal Tables が有効化されたテーブルに列を追加すると、履歴テーブルにも変更が反映され列が追加されます。

 ALTER TABLE Products ADD [商品カテゴリ] char(5);

列削除前	列削除後

また、Primary Key となる列や PERIOD 列以外の列を削除する場合も、同じく履歴テーブルにも変更が反映され列が削除されます。

 ALTER TABLE Products DROP COLUMN [フリガナ];

列削除前	列削除後

3. Temporal Tables の有効化について

Temporal Tables を有効化する際に既存の履歴テーブルを指定すると、現在のテーブルと履歴テーブルの両方に対してデータの整合性チェックが行われます。整合性チェックを手動で事前に実施しており、整合性チェックをスキップしたい場合などには　DATA_CONISTENCY_CHECK = OFF を追加パラメーターとして指定することにより整合性チェックをスキップできます。

不整合をチェックしないまま整合性チェックをスキップして Temporal Tables を有効化すると、例えば以下のような不整合が発生しますので、手動、自動のどちらかで整合性はチェックするようにしてください。

[履歴テーブルの方が Temporal Talbes を有効化したテーブルよりも新しい日時となっている場合]

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

前述の通り整合性チェックを外して Temporal Tables を有効化することはできます。

 ALTER TABLE [Products]   SET ( SYSTEM_VERSIONING = ON   ( HISTORY_TABLE = dbo.ProductsHistory, DATA_CONSISTENCY_CHECK = OFF  ) );

但し、データの整合性が合っていないため、現時点での有効データを参照しようとした際に、今回のケースでは”商品コード = 1” のデータが 2 件存在することになってしまっています。

 DECLARE @Day datetime2SET @Day = sysdatetime()SELECT 商品コード,商品名,sysstart,sysend FROM Products  FOR SYSTEM_TIME AS OF @DayWHERE 商品コード = 1 OR 商品コード = 2ORDER BY 商品コード,sysstart;GO

上記のような状況を避けるためにも、Temporal Tables を有効化する前には必ず手動、自動のどちらかで整合性チェックを実施するようにしてください。

以上、今回は SQL Server 2016 新機能の一つである Temporal Tables の設計時や開発時、運用時に知っておいていただきたいポイントをお伝えいたしました。

関連記事