SQL Serverで履歴管理をはじめよう！──初級者向け完全ガイド

データベースにおける「変更履歴管理」は、いつ誰がどのようにデータを更新したかを把握し、過去の状態に戻したり、不正な更新を検知したり、監査ログを残したりするうえで非常に重要です。

SQL Server には、簡単なトリガーを使った方法から、システムバージョニング（Temporal Table）を活用した高度な方法まで、多彩な履歴管理機能が用意されています。

本記事では、初級者向けに以下のポイントを詳しく解説します。

1. 既存テーブルへの履歴用カラム追加とトリガーによる管理
2. システムバージョニング（Temporal Table）を使った自動履歴管理
3. 具体的なサンプルシナリオとクエリ例
4. メリット・デメリットと運用上の注意点

これを読み終える頃には、実際のプロジェクトに即活用できる基礎知識とサンプルコードを手に入れ、SQL Server で自在に履歴管理をおこなえるようになります。

前提条件とサンプルテーブル

• SQL Server バージョン：2016 以降（Temporal Table 機能は 2016 から搭載）
• 管理ツール：SQL Server Management Studio（SSMS）
• サンプルテーブル：まずは簡単な「アクセスログ」テーブルを例に進めます。

-- データベース切替
USE LogAnalysis;
GO

-- サンプルテーブル作成
IF OBJECT_ID('dbo.AccessLogs', 'U') IS NOT NULL
  DROP TABLE dbo.AccessLogs;
GO

CREATE TABLE dbo.AccessLogs (
    LogID      INT          IDENTITY(1,1) PRIMARY KEY,
    LogDate    DATETIME2    NOT NULL,
    ClientIP   VARCHAR(45)  NOT NULL,
    Url        VARCHAR(200) NOT NULL,
    StatusCode INT          NOT NULL,
    UserAgent  VARCHAR(300) NULL
);
GO

このテーブルに対して「CreatedAt／UpdatedAt」を自動記録する方法と、さらに進んで Temporal Table による完全自動履歴管理方法を学んでいきましょう。

作成日時・更新日時カラムの追加とトリガー設定

カラム追加の方法

まずは、既存テーブルに「登録日時（CreatedAt）」と「更新日時（UpdatedAt）」の２カラムを追加します。

ALTER TABLE dbo.AccessLogs
ADD
    CreatedAt DATETIME2 NOT NULL
      CONSTRAINT DF_AccessLogs_CreatedAt DEFAULT SYSUTCDATETIME(),
    UpdatedAt DATETIME2 NOT NULL
      CONSTRAINT DF_AccessLogs_UpdatedAt DEFAULT SYSUTCDATETIME();
GO

SYSUTCDATETIME() は協定世界時（UTC）を高精度で返します。

INSERT 時には両カラムとも同じ値が自動設定されます。

UPDATEトリガーの作成

UpdatedAt はレコード更新時に上書きしたいので、AFTER UPDATE トリガーを作成します。

CREATE TRIGGER TR_AccessLogs_UpdateTimestamp
ON dbo.AccessLogs
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE L
    SET L.UpdatedAt = SYSUTCDATETIME()
    FROM dbo.AccessLogs AS L
    INNER JOIN inserted AS I
        ON L.LogID = I.LogID;
END;
GO

inserted テーブルには、更新後の行データが入っています。

トリガー内で UpdatedAt を最新の UTC 時刻に上書きします。

動作確認

-- レコード挿入テスト
INSERT INTO dbo.AccessLogs (LogDate, ClientIP, Url, StatusCode, UserAgent)
VALUES ('2025-06-22 15:00:00', '192.0.2.1', '/home', 200, 'TestAgent/1.0');

-- 最新レコード確認
SELECT TOP 1 LogID, CreatedAt, UpdatedAt
FROM dbo.AccessLogs
ORDER BY LogID DESC;

-- レコード更新テスト
UPDATE dbo.AccessLogs
SET StatusCode = 404
WHERE LogID = @@IDENTITY;  -- 直前登録ID

-- 更新後の日時確認
SELECT LogID, CreatedAt, UpdatedAt
FROM dbo.AccessLogs
WHERE LogID = @@IDENTITY;

最初の INSERT で CreatedAt＝UpdatedAtのレコードを挿入します。

UPDATE 実行後、UpdatedAt が新しい日時に変わっていることを確認しましょう。

システムバージョニング（Temporal Table）の基礎

Temporal Table とは

SQL Server のシステムバージョニング機能を使うと、INSERT／UPDATE／DELETE ごとに自動で履歴テーブルに過去の行を退避できる仕組みです。

• メリット：トリガーや手動クエリ不要で完全自動化。
• デメリット：履歴テーブルが肥大化しやすく、容量計画と運用ポリシーが必須。

メインテーブルと履歴テーブルの構成

Temporal Table を有効にするには、メインテーブルに以下の４つのカラムが必要です。

さらに、履歴用テーブルを用意するか、SQL Server に自動作成させる設定を行います。

Temporal Table の有効化手順

CREATE TABLE dbo.AccessLogs_History (
    LogID        INT          NOT NULL,
    LogDate      DATETIME2    NOT NULL,
    ClientIP     VARCHAR(45)  NOT NULL,
    Url          VARCHAR(200) NOT NULL,
    StatusCode   INT          NOT NULL,
    UserAgent    VARCHAR(300) NULL,
    SysStartTime DATETIME2    GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime   DATETIME2    GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
);

ALTER TABLE dbo.AccessLogs
ADD
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
      CONSTRAINT DF_AccessLogs_SysStart DEFAULT SYSUTCDATETIME(),
    SysEndTime   DATETIME2 GENERATED ALWAYS AS ROW END   NOT NULL
      CONSTRAINT DF_AccessLogs_SysEnd   DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
GO

ALTER TABLE dbo.AccessLogs
  SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AccessLogs_History));
GO

SysStartTime／SysEndTime は SQL Server が自動更新します。

SYSTEM_VERSIONING = ON にすると、更新／削除のたびに自動で AccessLogs_History に旧行を退避します。

自動履歴クエリ例

-- 2025-06-20 〜 2025-06-22 の間に有効だったデータ
SELECT *
FROM dbo.AccessLogs
FOR SYSTEM_TIME FROM '2025-06-20' TO '2025-06-22';

SELECT *
FROM dbo.AccessLogs
FOR SYSTEM_TIME ALL
WHERE LogID = 1
ORDER BY SysStartTime;

これらのクエリを使えば、過去の状態を容易に参照できます。

実践サンプル：社員マスタの履歴管理

実際の業務シナリオになぞらえて、社員情報テーブルで履歴管理に挑戦します。

テーブル設計例

CREATE TABLE dbo.Employee (
    EmployeeID   INT          IDENTITY(1,1) PRIMARY KEY,
    EmpName      NVARCHAR(100) NOT NULL,
    Department   NVARCHAR(50)  NOT NULL,
    Salary       DECIMAL(10,2) NOT NULL,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
      CONSTRAINT DF_Employee_SysStart DEFAULT SYSUTCDATETIME(),
    SysEndTime   DATETIME2 GENERATED ALWAYS AS ROW END   NOT NULL
      CONSTRAINT DF_Employee_SysEnd   DEFAULT CONVERT(DATETIME2,'9999-12-31 23:59:59.9999999'),
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
);
GO

ALTER TABLE dbo.Employee
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));
GO

データ投入＆更新シナリオ

-- 新入社員登録
INSERT INTO dbo.Employee (EmpName, Department, Salary)
VALUES ('山田 太郎', '営業部', 3500000),
       ('佐藤 花子', '経理部', 4000000);

-- 部署異動＆昇給シナリオ
UPDATE dbo.Employee
SET Department = 'マーケティング部', Salary = 3800000
WHERE EmployeeID = 1;

UPDATE dbo.Employee
SET Salary = 4200000
WHERE EmployeeID = 2;

過去データの参照クエリ

SELECT *
FROM dbo.Employee
FOR SYSTEM_TIME ALL
ORDER BY EmployeeID, SysStartTime;

-- 2025年7月1日時点の社員情報
SELECT *
FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2025-07-01';

運用時のポイントと注意点

ストレージ容量の考慮

• 履歴テーブルは更新・削除のたびに行が増えるため、期間経過後の古い履歴はアーカイブや削除ポリシーを設計しましょう。
• 定期的に古い履歴を別ストレージに移行するなどの運用が必要です。

保持期間ポリシーの設計

• 法規制（金融・医療など）や社内規定に従って、履歴保持期間を明文化しましょう。
• SQL Server Agent を使って定期的に古い履歴を削除するスクリプトをジョブ化すると便利です。

パフォーマンスチューニング

• メインテーブルと履歴テーブル両方に対してインデックスを適切に配置すると、クエリ性能が向上します。
• 大量履歴を持つ場合、パーティション機能の活用も検討してください。

まとめと次のステップ

ここまで、SQL Server における初級者向け履歴管理の二大方法──

1. トリガー＋タイムスタンプカラム方式
2. システムバージョニング（Temporal Table）方式

──を解説しました。

それぞれのメリット・デメリットを理解し、用途や運用ポリシーに合わせて選択してください。

ぜひ実際の開発／運用環境で試しつつ、履歴管理の重要性を実感してください。あなたのデータ品質と運用セキュリティが、より一層向上すること間違いなしです！