Azure SQL Databaseをソースとして差分連携を実施する際における行バージョンが有効な開始時刻列の活用 #SQL

概要

本記事では、Azure SQL database で更新日の監査列を行バージョンが有効な開始時刻（GENERATED ALWAYS AS ROW START）列により設定する方法を紹介します。Azure SQL database をソースとして差分連携する際、最終更新日に基づいてデータを抽出する必要があります。通常、更新日の監査列はプログラムにより設定されますが、ソース数が大量であるため、適切に設定されているかの確認が困難でした。そこで、最小限の変更で差分連携を行うために行バージョンが有効な開始時刻列を利用することにしました。この機能を利用することで、差分連携する際の抽出条件を行バージョンが有効な開始時刻列に共通化できるというメリットがあります。

引用元：CREATE TABLE (Transact-SQL) - SQL Server | Microsoft Learn

行バージョンが有効な開始時刻列の設定

行バージョンが有効な開始時刻（GENERATED ALWAYS AS ROW START）列は、テンポラルテーブル（システムバージョン管理されたテンポラルテーブル）機能を利用時に設定が必要なカラムであり、INSERT や UPDATE が実施された際にはその時点での UTC のタイムスタンプが設定されます。行バージョンが有効な開始時刻列を利用するためには、以下の SQL に示すように３つのカラムを設定する必要があります。これらのカラムは、テンポラルテーブル機能を設定しない場合でも動作し、INSERT と UPDATE の処理時刻を打刻します。今回は差分連携することを目的としているため、テンポラルテーブル機能は利用しないこと（SYSTEM_VERSIONING = OFF）としました。

    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

引用元：システムバージョン管理されたテンポラルテーブルを作成する - SQL Server | Microsoft Learn

行バージョンが有効な開始時刻（GENERATED ALWAYS AS ROW START）列を設定する際に既存のプログラムに影響を与えないために、HIDDENオプションを設定する必要があります。HIDDENオプションを設定しない場合には、INSERT 文において default を設定する必要があり、既存のプログラムの改修が必要となります。

引用元：システムバージョン管理のテンポラルテーブルのデータの変更 - SQL Server | Microsoft Learn

検証コードと実行結果

本記事では、次の２つのケースにおける検証コードと実行結果を記述します。

新規テーブルにおける行バージョンが有効な開始時刻列の利用
既存テーブルにおける行バージョンが有効な開始時刻列の利用

1. 新規テーブルにおける行バージョンが有効な開始時刻列の利用

テーブルの作成

DROP TABLE IF EXISTS UpadateTest01;
CREATE TABLE UpadateTest01 (
    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 PkUpadateTest01 PRIMARY KEY CLUSTERED (Id)
    )
    WITH (SYSTEM_VERSIONING = OFF);

INSERT の実行

-- Insert
INSERT INTO UpadateTest01 (
    Id,
    Name
    )
SELECT 1,
    'insert'
UNION ALL
SELECT 2,
    NULL
UNION ALL
SELECT 4,
    NULL;

UPDATE の実行

-- Update
WAITFOR DELAY '00:00:10';

UPDATE UpadateTest01
SET Name = 'update'
WHERE Id = 2;

MERGE の実行

-- Merge
WAITFOR DELAY '00:00:10';
DROP TABLE IF EXISTS #UpadateTest01Staging;
CREATE TABLE #UpadateTest01Staging (Id INT, Name varchar(50));

GO
INSERT INTO #UpadateTest01Staging VALUES (3, 'merge_insert');;
INSERT INTO #UpadateTest01Staging VALUES (4, 'merge_update');

MERGE UpadateTest01 AS target
USING #UpadateTest01Staging AS source
    ON (target.Id = source.Id)
WHEN MATCHED
    THEN
        UPDATE
        SET Name = source.Name
WHEN NOT MATCHED
    THEN
        INSERT (
            Id,
            Name
            )
        VALUES (
            source.Id,
            source.Name
            );

実行結果の確認

行バージョンが有効な開始時刻列（ValidFrom列）に実行時のタイムスタンプが設定されていることが確認できます。

SELECT *,
    ValidFrom,
    ValidTo
FROM UpadateTest01;

2. 既存テーブルにおける行バージョンが有効な開始時刻列の利用

1. テーブルの作成と INSERT 処理の実行

DROP TABLE IF EXISTS UpadateTest02;
CREATE TABLE UpadateTest02 (
    Id INT NOT NULL,
    Name VARCHAR(50)
    CONSTRAINT PkUpadateTest02 PRIMARY KEY CLUSTERED (Id)
    );

-- Insert
INSERT INTO UpadateTest02 (
    Id,
    Name
    )
SELECT 1,
    'pre_insert';

SELECT *
FROM UpadateTest02;

行バージョンが有効な開始時刻列等を追加

ALTER TABLE UpadateTest02
    ADD
        ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
            CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME(),
        ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
            CONSTRAINT DF_InsurancePolicy_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);
GO

ALTER TABLE UpadateTest02
    SET (SYSTEM_VERSIONING = OFF);
GO

INSERT 処理

-- Insert
WAITFOR DELAY '00:00:10';

INSERT INTO UpadateTest02 (
    Id,
    Name
    )
SELECT 2,
    'insert';

実行結果の確認

行バージョンが有効な開始時刻列（ValidFrom列）に実行時のタイムスタンプが設定されていることが確認できます。

SELECT *,
    ValidFrom,
    ValidTo
FROM UpadateTest02;

テーブルの削除

DROP TABLE IF EXISTS UpadateTest01;
DROP TABLE IF EXISTS UpadateTest02;