Azure SQL Database のテンポラルテーブル機能により DELETE されたレコードを取得する方法


## 概要

Azure SQL Database のテンポラルテーブル（システムバージョン管理テーブル）を活用し、削除されたレコードを特定する手法を紹介します。

## テンポラルテーブルに関する基本的な情報

テンポラルテーブルは、データの変更履歴を自動的に追跡し、任意の時点でのデータ状態を照会する機能を提供します。

テンポラルテーブルは、期間開始列と期間終了列を付与し、カレントテーブル（Current Table）と履歴テーブル（History Table）の2つのテーブルで管理します。最新のデータはカレントテーブルに保持され、削除されたレコードと更新前のレコードは履歴テーブルに保持されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/8f8351bb-5605-bfc8-4619-5ecf48394d42.png)

引用元：[テンポラル テーブル - SQL Server | Microsoft Learn](https://learn.microsoft.com/ja-jp/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16#how-does-temporal-work)

テンポラルテーブルに対する DELETE 操作が実行された際の仕様は、次のようにドキュメントに記載されています。具体的には、カレントテーブルからデータが削除され、そのデータは履歴テーブルに保持されます。

> Deletes: システムにより、行の前の値が履歴テーブルに格納され、ValidTo 列の値がシステム クロックに基づく現在のトランザクションの開始時間 (UTC タイム ゾーン) に設定されます。 これは行をクローズドとマークし、前の行が有効であった期間が記録されます。 現行テーブルでは、その行は削除されます。 現在のテーブルのクエリではこの行は返されません。 履歴データを処理するクエリのみで、クローズドの行のデータが返されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/10f9bff4-59f7-ea81-afb7-e5e26ecb8142.png)

引用元：[テンポラル テーブル - SQL Server | Microsoft Learn](https://learn.microsoft.com/ja-jp/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16#how-does-temporal-work)

削除されたレコードを取得するには、以下の2つの方法があります。どちらの方法も、カレントテーブルに存在せず、履歴テーブルにのみ存在するレコードを特定することが必要です。基本的には、性能上の観点から、履歴テーブルから削除されたレコードを取得することが望ましいです。ただし、匿名履歴テーブルを使用するテンポラルテーブルの場合は、テンポラルテーブルから削除されたレコードを取得することになります。

1. 履歴テーブルとカレントテーブルにて DELETE されたレコードを取得する方法
2. テンポラルテーブルとカレントテーブルにて DELETE されたレコードを取得する方法


## 事前準備

### テーブル作成

```sql
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
            )
    );

```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/89420750-6631-704d-09a9-02f573ae1c0d.png)

### テンポラルテーブルに対してデータ操作（INSERT、UPDATE、DELETE）の実行

```sql
-- 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
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/61d10f96-f94e-ad62-0ea5-0ea79ecb8cf4.png)

## データの確認

### テンポラルクエリの実行

`FOR SYSTEM_TIME ALL`句を記述することで、テンポラルテーブルのすべてのデータを表示します。`Id`列が`3`であるレコードが DELETE したレコードであり、期間終了列（`ValidTo`）列に削除した時点でのタイムスタンプが設定されています。

```sql
-- Temporal query
SELECT *,
    ValidFrom,
    ValidTo
FROM [dbo].[TempTableTest]
FOR SYSTEM_TIME ALL;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/3aa57e0a-3130-b949-437e-4f91592b3973.png)

### カレントテーブルへのクエリの実行

カレントテーブルのデータを表示すると、`Id`列が`3`であるレコードが存在しないことを確認できます。

```sql
-- Query to the current table
SELECT *,
    ValidFrom,
    ValidTo
FROM [dbo].[TempTableTest];
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/1983bf17-ec5b-a9a0-20a7-e2a879864fde.png)

### 履歴テーブルへのクエリの実行

履歴テーブルのデータを表示すると、`Id`列が`3`であるレコードが存在するを確認できます。

```sql
-- Query to the history table
SELECT *
FROM [dbo].[TempTableTestHistory];
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/a1f2274c-31ac-cfc9-e25e-33ad7930424a.png)

## DELETE されたレコードを取得

### 1. 履歴テーブルとカレントテーブルにて DELETE されたレコードを取得する方法

```sql
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;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/e716e584-6ab1-eb01-c765-2a5d08c9b77c.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/33cda2e6-5d6f-d5fd-0beb-47b02754c3bd.png)

### 2.テンポラルテーブルとカレントテーブルにて DELETE されたレコードを取得する方法

```sql
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;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/20daee5e-a44e-d4f8-afd3-d2e8205614ec.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/9e1c8d4a-20de-d7fe-6560-575c1cf2f722.png)

## リソースの削除

### 作成したテンポラルテーブルを削除

```sql
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;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/280d105a-d52b-cb9d-c7bb-4a23725a6e9d.png)