SQL DB にも Temporal Tables が来たので色々試しています。
Temporal Tables の機能や構文の日本語情報は、
自習書の No1 に少し載っています。
SQL Server 2016 | マイクロソフト クラウド プラットフォーム
英語だとこのへんかな
Creating a System-Versioned Temporal Table
Changing the Schema of a System-Versioned Temporal Table
今回は、検索クエリのハンズオンで使うために Temporal Table にあらかじめデータが入っている状態を作るためのメモです。
普通に Temporal Table を作成すると、開始と終了時刻は自前で設定出来ませんし、ヒストリーテーブルに任意のデータを追加する事が出来ません。
そのため、まずはベーステーブルとヒストリーテーブル2つを自前で用意します。
create table [社員マスタ] ( [Id] int not null primary key, [社員名] nvarchar(10) not null, [旧姓] nvarchar(10), [住所] nvarchar(10), [SysStart] datetime2 not null, [SysEnd] datetime2 not null ) create table [社員マスタHistory] ( [Id] int not null, [社員名] nvarchar(10) not null, [旧姓] nvarchar(10), [住所] nvarchar(10), [SysStart] datetime2 not null, [SysEnd] datetime2 not null )
ここでの注意点は、
- Temporal Table で必要な 日付のFrom-To カラム(今回は SysStart、SysEnd)を追加する
- ベーステーブルとヒストリーテーブルで同じカラム構成にする
- ベーステーブルには PK が必須、ヒストリーテーブルには PK が不要
- ハンズオンなので、SysStart、SysEnd を HIDDEN 指定していない
テーブルの準備が出来たのでデータを追加していきます。
まずはベーステーブルから。
insert into [社員マスタ] values (1, '田中 一郎', null, '大阪府', '2015-04-01 9:00:00', '9999-12-31 23:59:59.9999999'), (2, '鈴木 花子', null, '兵庫県', '2015-08-15 15:32:24', '9999-12-31 23:59:59.9999999'), (3, '伊藤 ゆかり', '田所', '奈良県', '2016-01-01 12:10:00', '9999-12-31 23:59:59.9999999'), (4, '佐藤 次郎', null, '京都府', '2015-09-01 9:00:00', '9999-12-31 23:59:59.9999999')
ベーステーブルには、最新のデータが入っているイメージです。
ここも注意点があって、
- Temporal Table で利用する終了時刻のカラムには、datetime2 の最大値を設定する
- Temporal Table で利用する開始時刻のカラムには、未来時刻はNG(Temporal Table 化する時点での未来時刻)
続いてヒストリーテーブルにデータを入れて、データの歴史を作っていきましょう。
insert into [社員マスタHistory] values (2, '一之瀬 花子', '鈴木', '兵庫県', '2013-02-23 13:34:33', '2015-08-15 15:32:24'), (3, '田所 ゆかり', null, '大阪府', '2014-10-14 20:12:22', '2016-01-01 12:10:00'), (5, '池田 三郎', null, '大阪府', '2013-05-17 9:00:00','2015-12-28 17:00:00'), (4, '佐藤 次郎', null, '兵庫県', '2012-04-01 9:00:00', '2015-09-01 9:00:00'), (5, '池田 三郎', null, '東京都', '2011-04-01 9:00:00', '2013-05-17 9:00:00'), (2, '鈴木 花子', null, '兵庫県', '2010-07-01 9:00:00', '2013-02-23 13:34:33')
注意点は、
- 開始時刻 <= 終了時刻
- 連続したデータを作る場合は、過去のデータの終了時刻の値が、次のデータの開始時刻の値と同じにする
select * from [社員マスタ] where [Id] = 2 union select * from [社員マスタHistory] where [Id] = 2 order by [SysStart] desc
こんな感じです。
データが出来たら Temporal Table にしてしまいます。
alter table [社員マスタ] add period for system_time ([SysStart], [SysEnd]) alter table [社員マスタ] set (system_versioning = on (history_table = [dbo].[社員マスタHistory]))
これであらかじめデータを追加した Temporal Table の出来上がり!
select * from [社員マスタ] select * from [社員マスタ] for system_time as of '2015-07-12'
クエリ全文はこちら
create table [社員マスタ] ( [Id] int not null primary key, [社員名] nvarchar(10) not null, [旧姓] nvarchar(10), [住所] nvarchar(10), [SysStart] datetime2 not null, [SysEnd] datetime2 not null ) create table [社員マスタHistory] ( [Id] int not null, [社員名] nvarchar(10) not null, [旧姓] nvarchar(10), [住所] nvarchar(10), [SysStart] datetime2 not null, [SysEnd] datetime2 not null ) -- データの追加 insert into [社員マスタ] values (1, '田中 一郎', null, '大阪府', '2015-04-01 9:00:00', '9999-12-31 23:59:59.9999999'), (2, '鈴木 花子', null, '兵庫県', '2015-08-15 15:32:24', '9999-12-31 23:59:59.9999999'), (3, '伊藤 ゆかり', '田所', '奈良県', '2016-01-01 12:10:00', '9999-12-31 23:59:59.9999999'), (4, '佐藤 次郎', null, '京都府', '2015-09-01 9:00:00', '9999-12-31 23:59:59.9999999') insert into [社員マスタHistory] values (2, '一之瀬 花子', '鈴木', '兵庫県', '2013-02-23 13:34:33', '2015-08-15 15:32:24'), (3, '田所 ゆかり', null, '大阪府', '2014-10-14 20:12:22', '2016-01-01 12:10:00'), (5, '池田 三郎', null, '大阪府', '2013-05-17 9:00:00','2015-12-28 17:00:00'), (4, '佐藤 次郎', null, '兵庫県', '2012-04-01 9:00:00', '2015-09-01 9:00:00'), (5, '池田 三郎', null, '東京都', '2011-04-01 9:00:00', '2013-05-17 9:00:00'), (2, '鈴木 花子', null, '兵庫県', '2010-07-01 9:00:00', '2013-02-23 13:34:33') alter table [社員マスタ] add period for system_time ([SysStart], [SysEnd]) alter table [社員マスタ] set (system_versioning = on (history_table = [dbo].[社員マスタHistory])) -- 後片付け alter table [社員マスタ] set (SYSTEM_VERSIONING = OFF) drop table [社員マスタHistory] drop table [社員マスタ]