【５分で流し読み】SQL Server / Azure SQL Databaseのテンポラルテーブルでできることを理解する

こんにちは。今日は、SQL Server / Azure SQL Databaseのテンポラルテーブルという機能を触ってみましたので、どういったことができるのか、何が嬉しいのか、分かったことを簡単にまとめておきたいと思います。

それではまいります。

テンポラルテーブルとは？

テンポラル テーブル (システム バージョン管理されたテンポラル テーブルともいう) は、現時点の正しいデータのみではなく、任意の時点でテーブルに格納されているデータに関する情報を提供するためのサポートが組み込まれているデータベース機能です。

テンポラル テーブル – SQL Server | Microsoft Docs

その他機能概要は以下。

• SQL Server 2016で登場した機能。Azure SQL Databaseでは2016年にPreviewとして登場した機能。
• 現在の情報を保持するテーブル（テンポラルテーブル）に加えて、履歴情報を保持するHistoryテーブルとセットで動作する
• 両方のテーブルで、データの有効期間情報を保持するためのシステム管理列（ValidFrom, ValidTo）が必要
• クエリ時には、FOR SYSTEM_TIME句で参照期間を指定することで、その時点のデータを読み出すことができる

テンポラルテーブルの使用シナリオ

テンポラル テーブルの使用シナリオ – SQL Server | Microsoft Docs

• データの監査（何が変更されたか、いつ変更されたかを追跡する必要がある場合）
• 特定時点の分析（時間とともにデータセットがどう変わったかを分析したい場合）
• データの行レベル破損の修復（バックアップを使うことなく効率的に修復できるよ、とのこと）

など・・

テンポラルテーブルの考慮事項と制約

テンポラル テーブルの考慮事項と制約 – SQL Server | Microsoft Docs

以下いくつかピックアップ。

• データ型 datetime2 を使用して、ValidFrom と ValidTo の値を記録するために使用する SYSTEM_TIME 期間列を定義する必要があります。
• 現在のテーブルがパーティション分割されている場合、パーティション分割構成が現在のテーブルから履歴テーブルに自動的にレプリケートされないため、履歴テーブルは既定のファイル グループに作成されます。
• テンポラル テーブルでは、 (n)varchar(max) 、varbinary(max) 、 (n)text、image などの BLOB データ型がサポートされていますが、これらは多大なストレージ コストを発生させ、サイズが多いためにパフォーマンスに影響を与えます。 そのため、システムの設計時に、これらのデータ型を使用する場合は注意が必要です。
• 履歴テーブルのデータを直接変更することはできません。

まあ、上記のような注意点はありつつも、こういう機能は特にパフォーマンスとのトレードオフが気になりますよね。以下に実際に検証してみている記事があって参考になりましたので貼っておきます。

SQLServer テンポラルテーブルを検証してみた (sint.co.jp)

実際に作成してみた

今回、以下の手順をなぞってみました。

システムバージョン管理を有効にしたテーブルを作成する

システム バージョン管理されたテンポラル テーブルを作成する – SQL Server | Microsoft Docs

  CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED
  , DeptName VARCHAR(50) NOT NULL
  , ManagerID INT NULL
  , ParentDeptID INT NULL
  , ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
  , ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);

なお、既存のテーブルをシステムバージョン管理テーブルに変更する手順も以下にあります。

システム バージョン管理されたテンポラル テーブルを作成する – SQL Server | Microsoft Docs

データを追加する

INSERT INTO [dbo].[Department]
  (  [DeptID]
        , [DeptName]
        , [ManagerID]
        ,[ParentDeptID]
  )
     VALUES
       (  10
       , 'Marketing'
       , 101
       , 1
       ) ;

この地点ではHistoryテーブルには履歴データは追加されていませんでした。

データを更新する

続けて、レコードを更新してみます。

UPDATE [dbo].[Department] SET [ManagerID] = 501 WHERE [DeptID] = 10

すると、Historyテーブルの方に、変更前のデータと、有効期間を含んだレコードが追加されました。

過去時点のデータをクエリする

過去時点のデータは、テンポラルテーブルに対してFOR SYSTEM_TIME句で期間を指定してやると取得することができます。（内部的には、Historyテーブルを参照しているのでしょう）

SELECT TOP (1000) [DeptID]
		  ,[DeptName]
		  ,[ManagerID]
		  ,[ParentDeptID]
		  ,[ValidFrom]
		  ,[ValidTo]
FROM [AdventureWorks2019].[dbo].[Department]
FOR SYSTEM_TIME BETWEEN '2022-07-03 06:20:00.0000000' AND '2022-07-03 06:25:00.0000000'

以上、テンポラルテーブルについての簡単なまとめでした。

実際のところ、使っている人は多いんだろうか？SQL Serverの監査系機能はまだ勉強中で全体像が見えてないですが、パット見てみた感じ似たようなことができそうな機能もありそうだったので、そのあたりについてもまた調べて、どういった場合にどの機能を使うとよいのか頭を整理していきたいと思います。

データ変更の追跡 – SQL Server | Microsoft Docs

少しでも参考になりましたら幸いです。

おしまい