テストデータを仕込んだ Temporal Tables を作成する

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 [社員マスタ]