先日、SQL Server 2016 CTP2 が公開されました。2016になって色々な新機能が追加されていますが、その1つである「Temporal テーブル」について確認してみました。
Temporal テーブル って何？
レコードの更新履歴をRDBMS側で自動的に記録してくれるテーブル、のことっぽいです。
自動的に記録してくれるだけでなく、特定時点のデータをSQLで抽出することも当然できます。
Temporal Tables
うっかりDELETEしちゃった！（うっかりすぎる）なんて場合でも、DELETE前のデータを見ることができたりするわけですね。
世の中にはDELETE前のデータを見ることができたりするお高いツールもあったり（これとかこれとか）しますが、そんなもんいらんかったんや！……となるのかどうか。
とりあえず使ってみる
まず、こんな感じにテーブルを作ります。
CREATE TABLE temporaltest (
id CHAR(5) NOT NULL PRIMARY KEY
,name VARCHAR(10) NOT NULL
,starttime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL -- (1)
,endtime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL -- (2)
,PERIOD FOR SYSTEM_TIME (starttime,endtime) -- (3)
) WITH (SYSTEM_VERSIONING=ON); -- (4)
idとnameだけのシンプルなテーブルに、データの有効期限を表わす列を2つ（starttime,endtime）追加しました。
(1)(2)GENERATED ALWAYS AS ROW START/ENDと(3)PERIOD FOR SYSTEM_TIMEをつけることにより、starttime列とendtime列がそれぞれ開始日時と終了日時を表わすフィールドになります。
(4)WITH (SYSTEM_VERSIONING=ON)をつけると、裏で履歴管理用のテーブルを自動生成してくれます。
テーブルができたら、適当にデータ更新をしてみましょう。
--14:24頃に実行
INSERT INTO temporaltest(id,name) VALUES('00001','テスト1'),('00002','test 2'),('00003','てすと３');
--14:26頃に実行
UPDATE temporaltest SET name='TEST II' WHERE id='00002';
--14:30頃に実行
DELETE FROM temporaltest WHERE id='00002';
--14:35頃に実行
INSERT INTO temporaltest(id,name) VALUES('00002','復活2');
--14:36頃に実行
UPDATE temporaltest SET id='00013',name='元てすと３' WHERE id='00003';
まず普通にSELECTします。
SELECT id,name FROM temporaltest
ORDER BY id;
| id | name |
|---|---|
| 00001 | テスト1 |
| 00002 | 復活2 |
| 00013 | 元てすと３ |
普通ですね。
では14:24頃のINSERT文実行直後のデータを見てみましょう。
なお、日時は(5)のようにUTCに変換します。日本なら-9時間すればOKです。
SELECT id,name FROM temporaltest
FOR SYSTEM_TIME AS OF '2015-05-29 05:24:30' -- (5)
ORDER BY id;
| id | name |
|---|---|
| 00001 | テスト1 |
| 00002 | test 2 |
| 00003 | てすと３ |
(5)の値をいろいろ変えてみると、それに合わせて結果が変わることが確認できます。
FOR SYSTEM_TIME AS OFで特定の日時を指定する以外にも、FOR SYSTEM_TIME FROM … TO …、FOR SYSTEM_TIME BETWEEN … AND …、FOR SYSTEM_TIME CONTAINED IN (…)といった指定ができます。
「お高いツールなんていらんかったんや！」と、なる？
んー、どうでしょう？
いろんなテーブルを事前に Temporal にしておけば、要らなくなるかもしれません。
でもTemporalとして運用し続けるにはそれなりのコスト（処理のオーバーヘッドとか履歴データ分のディスク容量とか）が必要なわけで、その辺をどう考えるか次第ではないでしょうか。
そして、Temporalにしていないテーブルに限ってうっかりDELETEが発生したりするような気もｗ
余談
- TemporalはSQL:2011で規格化されている
- Oracleは同様な機能を12cで既に実装している
つーことは、今後ほかのRDMBSでも実装されるかも？