SE の雑記

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

SQL Database で Temporal テーブルが使えるようになったので改めて試してみる

leave a comment

Preview: Temporal tables in Azure SQL Database でアナウンスされていますが、SQL Database v12 で、プレビューではありますが、Temporal テーブルが使用できるようになりました。

以前、SQL Server 2016 CTP 2.0 の Temporal Table を使ってみる で試してみたのですが、ざっくりでしたのでこの機会に改めて、試してみたいと思います。

現状の SQL Database では、互換性レベルは 120 / 130 を使用することができますが、Temporal テーブルについては、120 でも使用することができます。
# 互換性レベルについては、現状 100 ～ 130 が使用できるようですが、実際に使用するのは 120 / 130 ぐらいかと。

Temporal テーブルの詳細については Temporal Tables に記載されていますが、テーブルの構成を

  • 最新のレコードを保持するテーブル
  • DELETE / UPDATE によって変更されたレコードを保持する履歴テーブル

というように、二つのテーブルで構成し、システムでバージョン管理されたテーブルとして、レコードを管理するための機能となります。

テーブルには、「SysStartTime」「SysEndTime」の二つの項目を持ち、データの存在期間を保持することで、特定のタイミングで存在していたデータの確認を行うことができます。

■テーブルの作成

実際のテーブルの構造としては以下のようになります。
image

 

Temporal テーブルについては、テーブルに「システム バージョン管理」「履歴」の 2 つのテーブルで構成されていることが確認できます。

それでは、実際のテーブルの作成方法を見ていきたいと思います。
単純なパターンでは、以下のクエリでテーブルを作成することができます。
なお、Temporal テーブルで使用するテーブルについては、プライマリキーが設定されている必要があります。

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

 

Temporal テーブル は「SYSTEM_VERSIONING = ON」を設定することで作成ができ、どの日付データをシステムタイムとして使用するかを、「PERIOD FOR SYSTEM_TIME」として指定を行います。

今回は、「SysStartTime」「SysEndTime」を指定しています。

「SysStartTime」には、「GENERATED ALWAYS AS ROW START」、「SysEndTime」には、「GENERATED ALWAYS AS ROW END」を指定することで、行が生成された日付を自動的に設定するようにします。

また、上記の列は、内部的に利用するものになりますので、SELECT * を使用した場合などにの検索には、不要となるケースがあります。

検索の結果として不要な場合は、「HIDDEN」を設定しておくことで、通常の検索結果から除外することができるようになります。

実際に作成されたテーブルがこちらです。

image

単純に「SYSTEM_VERSIONING = ON」を設定した場合は、同一の構成を持つテーブルが自動的に生成された状態になります。

テーブル名を指定したい場合には、手動で履歴テーブルを作成し、そのテーブルを使用するように設定をします。

-- 履歴用テーブルの作成
CREATE TABLE TemporalTableHistory(
Col1 int NOT NULL,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 nvarchar(10),
SysStartTime datetime2 NOT NULL,
SysEndTime datetime2 NOT NULL
)
GO
-- ベーステーブルの作成
CREATE TABLE TemporalTable(
Col1 int IDENTITY PRIMARY KEY CLUSTERED,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 nvarchar(10),
SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL ,
SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL ,
PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTableHistory))
GO

このような方法を使用することで、テーブル名を制御して Temporal テーブルを作成することができます。

 

■テーブルの削除

システム バージョン管理されたテーブルは単純な DROP TABLE では削除をすることができません。

メッセージ 13552、レベル 16、状態 1、行 1

Drop table operation failed on table ‘ComCamp.dbo.TemporalTable’ because it is not supported operation on system-versioned temporal tables.

Temporal テーブルを削除したい場合は、システム バージョン管理されたテーブルを一度無効にする必要があります。

ALTER TABLE TemporalTable SET (SYSTEM_VERSIONING = OFF)

 

これにより、システム バージョン管理されたテーブルとしての関連付けが外れ、それぞれのテーブルが単一のテーブルとしてパージされ、DROP TABLE が実行できるようになります。

 

■テーブルの関連付け

システム バージョン管理されたテーブルですが、CREATE TABLE / ALTER TABLE でテーブルの関連付けについて設定することができます。

CREATE TABLE (Transact-SQL)

<table_option> ::= {

[MEMORY_OPTIMIZED = {ON | OFF}] |

[DURABILITY = {SCHEMA_ONLY | SCHEMA_AND_DATA}]   | 
[ SYSTEM_VERSIONING = ON [ ( HISTORY_TABLE = schema_name . history_table_name [, DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ] ]

}

ALTER TABLE (Transact-SQL)

| SET         (            
[ FILESTREAM_ON =      { partition_scheme_name | filegroup | "default" | "NULL" } ]             |

SYSTEM_VERSIONING =                   {                       OFF                   | ON                       [ ( HISTORY_TABLE = schema_name . history_table_name                           [, DATA_CONSISTENCY_CHECK = { ON | OFF } ]                         )                       ]                   }           )

 

ポイントとなるのは、「DATA_CONSISTENCY_CHECK」でしょうか。

これは、データ整合性を確認するためのオプションとなりますが、以下のようなケースで使用することになるかと。

  • 既にデータが格納されているテーブルをシステム バージョン管理されたテーブルとして利用する

履歴テーブルに、すでにデータが入っている状態にしてみます。

システム バージョン管理されたテーブルのバージョン管理に使用する日付のデータは UTC で格納されているため、UTC の日付関数を使用してデータを挿入しています。

DROP TABLE IF EXISTS TemporalTableHistory
CREATE TABLE TemporalTableHistory(
Col1 int NOT NULL,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 nvarchar(10),
SysStartTime datetime2 NOT NULL,
SysEndTime datetime2 NOT NULL
)
GO
INSERT INTO TemporalTableHistory (Col1, Col2, Col3, Col4, SysStartTime, SysEndTime)
VALUES (1, NEWID(), NEWID(), N'AAAAAA', SYSUTCDATETIME(), DATEADD(ss, 10, SYSUTCDATETIME()))
INSERT INTO TemporalTableHistory (Col1, Col2, Col3, Col4, SysStartTime, SysEndTime)
VALUES (2, NEWID(), NEWID(), N'AAAAAA', SYSUTCDATETIME(), DATEADD(ss, 10, SYSUTCDATETIME()))

 

このテーブルをシステム バージョン管理されたテーブルの履歴テーブルとして指定する場合は以下のようなクエリで実施可能です。

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

 

UTC の日付を基準として、未来のデータが格納されている場合は、テーブルを関連付けしようとした場合に、以下のようなエラーとなります。

メッセージ 13543、レベル 16、状態 0、行 30

Setting SYSTEM_VERSIONING to ON failed because history table ‘ComCamp.dbo.TemporalTableHistory’ contains invalid records with end of period set to a value in the future.

このような場合は「DATA_CONSISTENCY_CHECK」を「OFF」にして、テーブルの関連付けを行います。

これで、履歴テーブルに未来のデータが入っている状態でも関連付けが行えます。

# このケースはなさそうですが。。。

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

 

手動で日付を設定する場合、開始と終了の日付はずらしておく必要があります。

以下は、全期間のデータを取得するためのクエリとなります。

SELECT * FROM TemporalTable FOR SYSTEM_TIME ALL

 

この時の実行プランですが、以下のようになっています。

image

履歴テーブルの「SysStartTime」<>「SysEndTime」という制限がついていますね。

先ほどデータを投入したクエリでは「DATEADD」を使用して、開始と終了の日付を操作していました。

これは、開始と終了の日付に同一の日付を指定してしまった場合、SYSTEM_ITME_ALL でヒットしなくなるための対応です。

データを手動で作成する場合は、開始と終了の日付をずらすのを意識しておいた方がよいかと。

もう一つのパターンとして、ALTER TABLE を使用したケースを。

元になるテーブルを以下のような状態で作成していたとします。

CREATE TABLE TemporalTable(
Col1 int IDENTITY PRIMARY KEY CLUSTERED,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 nvarchar(10))
INSERT INTO TemporalTable (Col2, Col3, Col4) VALUES (NEWID(), NEWID(), N'AAAAAA')
INSERT INTO TemporalTable (Col2, Col3, Col4) VALUES (NEWID(), NEWID(), N'AAAAAA')


Temporal テーブルを利用していないで、テーブルを作成したケースを想定しています。

このテーブルを Temporal テーブル化するためには、ALTER テーブルを使用して、必要な列を追加します。

ALTER TABLE TemporalTable
ADD
SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN
CONSTRAINT DF_TemporalTable_SysStartTime DEFAULT DATEADD(second, -1, SYSUTCDATETIME()) ,
SysEndTime datetime2  GENERATED ALWAYS AS ROW END HIDDEN
CONSTRAINT DF_TemporalTable_SysEndTime DEFAULT '9999.12.31 23:59:59.9999999',
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)

 

その後、履歴テーブルと関連付けます。

ALTER TABLE TemporalTable
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTableHistory))

 

データが入っているテーブルを使用するかどうかは、意識しておいた方がよさそうですね。

 

■テーブルの変更

テーブルの変更関連の動作をざっくりとみていきたいと思います。

SQL Server 2016 の CTP の当初では、結構制約があったはずなのですが、今は緩和されているものがあるかと。

現状、ALTER TABLE はサポートされていますので、以下のような変更は実施可能です。

ALTER TABLE dbo.TemporalTable ADD Col99 uniqueidentifier NULL
ALTER TABLE dbo.TemporalTable ALTER COLUMN  Col4 nchar(20) NULL
ALTER TABLE dbo.TemporalTable DROP COLUMN  Col4

 

以下のパターンのインデックスの操作も可能ですね。

CREATE INDEX IX_TemporalTableHistory_Col3 ON TemporalTableHistory(Col3)
DROP INDEX IX_TemporalTableHistory_Col3 ON TemporalTableHistory

 

TRUNCATE TABLE が実行できないという制約は変わらないようですね。

 

■テーブルの検索

検索については、FROM (Transact-SQL) の拡張機能を使用します。

<system_time> ::=

{      AS OF <date_time>     |  
FROM <start_date_time> TO <end_date_time>     |  
BETWEEN <start_date_time> AND <end_date_time>     |  
CONTAINED IN (<start_date_time> , <end_date_time>) |

ALL }     <date_time>::=         <date_time_literal> |

@date_time_variable     <start_date_time>::=         <date_time_literal> |

@date_time_variable     <end_date_time>::=         <date_time_literal> |

@date_time_variable

日付を HIDDEN にした場合は、明示的に列を指定しないと検索が行われないのも覚えておくとよさそうですね。

-- 全期間のデータを検索
SELECT * FROM TemporalTable
FOR SYSTEM_TIME ALL
SELECT *,SysStartTime,SysEndTime FROM TemporalTable
FOR SYSTEM_TIME ALL

 

日付の個所に関してはトランザクションの発生タイミングが影響するので、データの確認についてはこの辺を意識しておいた方がよさそうですね。

SET NOCOUNT ON
GO
-- テストデータの確認
INSERT INTO TemporalTable (Col2, Col3, Col4)
VALUES(NEWID(), NEWID(), N'AAAAAA'),
(NEWID(), NEWID(), N'AAAAAA'),
(NEWID(), NEWID(), N'AAAAAA'),
(NEWID(), NEWID(), N'AAAAAA')
INSERT INTO TemporalTable (Col2, Col3, Col4) VALUES (NEWID(), NEWID(), N'BBBBBB')
INSERT INTO TemporalTable (Col2, Col3, Col4) VALUES (NEWID(), NEWID(), N'BBBBBB')
BEGIN TRAN
INSERT INTO TemporalTable (Col2, Col3, Col4) VALUES (NEWID(), NEWID(), N'CCCCCC')
INSERT INTO TemporalTable (Col2, Col3, Col4) VALUES (NEWID(), NEWID(), N'CCCCCC')
COMMIT TRAN
BEGIN TRAN
INSERT INTO TemporalTable (Col2, Col3, Col4) VALUES (NEWID(), NEWID(), N'DDDDDD')
WAITFOR DELAY '00:00:03'
INSERT INTO TemporalTable (Col2, Col3, Col4) VALUES (NEWID(), NEWID(), N'DDDDDD')
COMMIT TRAN
SELECT *,SysStartTime,SysEndTime FROM TemporalTable
FOR SYSTEM_TIME ALL
DELETE FROM TemporalTable
SELECT *,SysStartTime,SysEndTime FROM TemporalTable
FOR SYSTEM_TIME ALL

 

image

 

以前の投稿ではカバーできていなさそうなものをいろいろと。

Share

Written by Masayuki.Ozawa

2月 28th, 2016 at 11:53 am

Posted in SQL Database,SQL Server

Tagged with , , ,

«
»

Leave a Reply