SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2016 CTP 2.0 の Temporal Table を使ってみる

one comment

SQL Server 2016 では Temporal Table というシステムバージョン管理されたテーブルを作成することができます。
Temporal Table ではすべての行情報のバージョニングを行うテーブルとなり、データがどのように更新されているか等を確認することができます。

詳細については Temporal TablesCREATE TABLE (Transact-SQL) を参照してください。

基本的な仕組みとしては、

  • システムバージョン管理されたテーブル
  • 履歴テーブル

の 2 つを透過的に見ることができる仕組みになるかと。

新規に Temporal Table を作成する場合は以下のようなクエリを実行します。

CREATE TABLE TemporalTable(
Col1 int IDENTITY PRIMARY KEY CLUSTERED,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON)

 

これにより、テーブルが以下のように作成されます。

image

テーブル作成時に「SYSTEM_VERSIONING = ON」で作成すると「MSSQL_TemporalHistoryFor_xxxxx」で履歴テーブルが作成されるため、履歴テーブルのテーブル名を制御したい場合には、以下のように履歴テーブルを最初に作成してから関連付けるようにします。

履歴テーブルを事前に作成しない状態で、履歴テーブル名を指定した場合 (HISTORY_TABLE =)、自動的に履歴テーブルが指定した名称で作成されるようですね。

CREATE TABLE TemporalTableHistory(
Col1 int NOT NULL,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
SysStartTime datetime2 NOT NULL,
SysEndTime datetime2 NOT NULL
)

CREATE TABLE TemporalTable(
Col1 int IDENTITY PRIMARY KEY CLUSTERED,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTableHistory))

 

履歴テーブルについては

  • NULL 許容が同じ状態になっている
  • 列の情報が同じになっている

というような制約がありますので、基本としては同じ構成のテーブル構造とする必要があります。

また、履歴テーブルについては同一キーの複数の情報が設定されますので、プライマリキーが設定できない形になるようです。

このように作成することで、以下のようにテーブル名を制御することができます。

image

既存のテーブルを Temporal Table にする場合には、

  • GENERATED ALWAYS AS ROW START NOT NULL の列を追加
  • GENERATED ALWAYS AS ROW END NOT NULL の列を追加
  • SYSPERIOD FOR SYSTEM_TIME を設定
  • SYSTEM_VERSIONING を ON

にすることで、Temporal Table 化をすることができます。

それでは、テーブルにデータを入れてみたいと思います。

INSERT INTO TemporalTable (Col2, Col3)
VALUES(NEWID(), NEWID()), (NEWID(), NEWID()), (NEWID(), NEWID()), (NEWID(), NEWID())

現状は以下のようにデータが入っています。

image

それではデータを変更してみたいと思います。

UPDATE TemporalTable SET Col2 = NEWID() WHERE Col1 = 1

SysStartTime が変更されているのが確認できますね。

image

変更をしたことで自動的に履歴テーブルにデータが格納されます。

image

いつからいつまで存在していたというデータとして確認をすることができますね。

Temporal Table ではバージョン管理で使用されている日付でデータを検索することができます。

  • FOR SYSTEM_TIME AS OF <date_time_literal_or_variable>
  • FOR SYSTEM_TIME FROM <date_time_literal_or_variable> TO <date_time_literal_or_variable>
  • FOR SYSTEM_TIME BETWEEN <date_time_literal_or_variable> AND <date_time_literal_or_variable>
  • CONTAINED IN (<date_time_literal_or_variable> , <date_time_literal_or_variable>)

内部のバージョン日付については UTC で管理されていますので、それを意識して検索をする必要があります。

特定のタイミングのデータを検索するのであれば、

SELECT * FROM TemporalTable
FOR SYSTEM_TIME AS OF '2015-05-30 08:48:19.0135142'

という形で検索をすれば、以下のように履歴テーブルも透過的に検索された状態でデータが取得されます。

image

 

SELECT * FROM TemporalTable
FOR SYSTEM_TIME BETWEEN '2015-05-30 08:50' AND '2015-05-30 09:00'

と実行すると、UTC の時間で「8:50」~「9:00」でアクティブだったデータということで、以下のような結果となります。

# 1 の履歴のデータは「8:48」~「8:49」までアクティブだったデータになりますので除外されます。

image

このような形で Temporal Table は透過的に検索ができるようになっています。

システムバージョン管理されたテーブルへの変更を検知し、履歴テーブルに INSERT をする仕組みとなっていそうですので、設定によるオーバーヘッドはありそうです。

履歴テーブルについては、異なるファイルグループに配置することができるようなので、ファイル配置でうまく負荷を分散させることはできそうですが。

また、設定をすることで ALTER TABLE の一部の操作の制限もかかるようですので適用時には運用に影響はないかを考慮する必要がありそうですね。

Written by masayuki.ozawa

5月 30th, 2015 at 6:24 pm

Posted in SQL Server

Tagged with ,

One Response to 'SQL Server 2016 CTP 2.0 の Temporal Table を使ってみる'

Subscribe to comments with RSS or TrackBack to 'SQL Server 2016 CTP 2.0 の Temporal Table を使ってみる'.

  1. […] 詳細:SQL Server 2016 CTP 2.0 の Temporal Table を使ってみる […]

Leave a Reply

*