概要
Azure SQL Database のテンポラルテーブル（システムバージョン管理テーブル）を活用し、削除されたレコードを特定する手法を紹介します。
テンポラルテーブルに関する基本的な情報
テンポラルテーブルは、データの変更履歴を自動的に追跡し、任意の時点でのデータ状態を照会する機能を提供します。
テンポラルテーブルは、期間開始列と期間終了列を付与し、カレントテーブル（Current Table）と履歴テーブル（History Table）の2つのテーブルで管理します。最新のデータはカレントテーブルに保持され、削除されたレコードと更新前のレコードは履歴テーブルに保持されます。
引用元：テンポラル テーブル - SQL Server | Microsoft Learn
テンポラルテーブルに対する DELETE 操作が実行された際の仕様は、次のようにドキュメントに記載されています。具体的には、カレントテーブルからデータが削除され、そのデータは履歴テーブルに保持されます。
Deletes: システムにより、行の前の値が履歴テーブルに格納され、ValidTo 列の値がシステム クロックに基づく現在のトランザクションの開始時間 (UTC タイム ゾーン) に設定されます。 これは行をクローズドとマークし、前の行が有効であった期間が記録されます。 現行テーブルでは、その行は削除されます。 現在のテーブルのクエリではこの行は返されません。 履歴データを処理するクエリのみで、クローズドの行のデータが返されます。
引用元：テンポラル テーブル - SQL Server | Microsoft Learn
削除されたレコードを取得するには、以下の2つの方法があります。どちらの方法も、カレントテーブルに存在せず、履歴テーブルにのみ存在するレコードを特定することが必要です。基本的には、性能上の観点から、履歴テーブルから削除されたレコードを取得することが望ましいです。ただし、匿名履歴テーブルを使用するテンポラルテーブルの場合は、テンポラルテーブルから削除されたレコードを取得することになります。
- 履歴テーブルとカレントテーブルにて DELETE されたレコードを取得する方法
- テンポラルテーブルとカレントテーブルにて DELETE されたレコードを取得する方法
事前準備
テーブル作成
IF OBJECT_ID('dbo.TempTableTest', 'U') IS NOT NULL
ALTER TABLE TempTableTest SET (SYSTEM_VERSIONING = OFF);
DROP TABLE IF EXISTS TempTableTest;
DROP TABLE IF EXISTS TempTableTestHistory;
CREATE TABLE dbo.TempTableTest (
Id INT NOT NULL,
Name VARCHAR(50),
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo),
CONSTRAINT PK_TempTableTest PRIMARY KEY CLUSTERED (Id)
)
WITH (
SYSTEM_VERSIONING = ON
(
HISTORY_TABLE = dbo.TempTableTestHistory,
HISTORY_RETENTION_PERIOD = 2 WEEKS
)
);
テンポラルテーブルに対してデータ操作（INSERT、UPDATE、DELETE）の実行
-- insert
INSERT INTO [dbo].[TempTableTest] (
Id,
Name
)
VALUES (
1,
'a'
);
Go
-- update
INSERT INTO [dbo].[TempTableTest] (
Id,
Name
)
VALUES (
2,
NULL
);
UPDATE [dbo].[TempTableTest] SET Name = 'b' WHERE Id = 2;
Go
-- delete
INSERT INTO [dbo].[TempTableTest] (
Id,
Name
)
VALUES (
3,
'c'
);
Go
DELETE TempTableTest
WHERE Id = 3;
GO
データの確認
テンポラルクエリの実行
FOR SYSTEM_TIME ALL句を記述することで、テンポラルテーブルのすべてのデータを表示します。Id列が3であるレコードが DELETE したレコードであり、期間終了列（ValidTo）列に削除した時点でのタイムスタンプが設定されています。
-- Temporal query
SELECT *,
ValidFrom,
ValidTo
FROM [dbo].[TempTableTest]
FOR SYSTEM_TIME ALL;
カレントテーブルへのクエリの実行
カレントテーブルのデータを表示すると、Id列が3であるレコードが存在しないことを確認できます。
-- Query to the current table
SELECT *,
ValidFrom,
ValidTo
FROM [dbo].[TempTableTest];
履歴テーブルへのクエリの実行
履歴テーブルのデータを表示すると、Id列が3であるレコードが存在するを確認できます。
-- Query to the history table
SELECT *
FROM [dbo].[TempTableTestHistory];
DELETE されたレコードを取得
1. 履歴テーブルとカレントテーブルにて DELETE されたレコードを取得する方法
SELECT tgt.*
FROM [dbo].[TempTableTestHistory] tgt
LEFT OUTER JOIN [dbo].[TempTableTest] src
ON tgt.Id = src.Id
WHERE tgt.ValidTo < '9999-12-31 23:59:59.9999999'
AND src.Id IS NULL;
2.テンポラルテーブルとカレントテーブルにて DELETE されたレコードを取得する方法
SELECT tgt.*,
tgt.ValidFrom,
tgt.ValidTo
FROM [dbo].[TempTableTest]
FOR SYSTEM_TIME ALL tgt
LEFT OUTER JOIN [dbo].[TempTableTest] src
ON tgt.Id = src.Id
WHERE tgt.ValidTo < '9999-12-31 23:59:59.9999999'
AND src.Id IS NULL;
リソースの削除
作成したテンポラルテーブルを削除
IF OBJECT_ID('dbo.TempTableTest', 'U') IS NOT NULL
ALTER TABLE TempTableTest SET (SYSTEM_VERSIONING = OFF);
DROP TABLE IF EXISTS TempTableTest;
DROP TABLE IF EXISTS TempTableTestHistory;
