Microsoft SQL Server
一時テーブル

備考

テンポラルテーブルの作成

CREATE TABLE dbo.Employee   
(    
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED   
  , [Name] nvarchar(100) NOT NULL  
  , [Position] varchar(100) NOT NULL   
  , [Department] varchar(100) NOT NULL  
  , [Address] nvarchar(1024) NOT NULL  
  , [AnnualSalary] decimal (10,2) NOT NULL  
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END  
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)  
 )    
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));  

インサートは：INSERTには、システムは、システムクロックに基づいて、（UTCタイムゾーンで）現在のトランザクションの開始時にValidFrom列の値を設定し、9999-の最大値にValidTo列の値を割り当てます12-31。行が開いているとマークします。

UPDATES： UPDATEで、システムは履歴テーブルの行の前の値を保存し、システムクロックに基づいてValidTo列の値を現在のトランザクションの開始時刻（UTCタイムゾーン）に設定します。これは、行が有効であった期間を記録して、その行を閉じたものとしてマークします。現在のテーブルでは、行が新しい値で更新され、システムはシステム時計に基づいてValidFrom列の値をトランザクションの開始時間（UTCタイムゾーン）に設定します。現在のテーブルのValidTo列の更新された行の値は、9999-12-31の最大値のままです。

削除します 。DELETEで、システムは、履歴テーブルの行の前の値を記憶し、システムクロックに基づいて（UTCタイムゾーンで）現在のトランザクションの開始時にValidTo列の値を設定します。これは、行を閉じたものとしてマークし、前の行が有効であった期間を記録します。現在のテーブルでは、行は削除されます。現在のテーブルのクエリはこの行を返しません。履歴データを処理するクエリのみが、行が閉じられているデータを返します。

MERGE ： MERGEでは、 MERGEステートメントのアクションとして指定されているものに応じて、操作は3つまでのステートメント（ INSERT 、 UPDATE 、および/またはDELETE ）が実行された場合とまったく同じように動作します。

ヒント：システムのdatetime2列に記録される時間は、トランザクション自体の開始時間に基づいています。たとえば、1つのトランザクション内に挿入されたすべての行は、 SYSTEM_TIME期間の開始に対応する列に記録された同じUTC時間を持ちます。

時間データをどのように照会するのですか？

SELECT * FROM Employee   
    FOR SYSTEM_TIME    
        BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'   
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;  

特定の時点で指定された実際の値を返します（FOR SYSTEM_TIME AS OF ）

過去に指定された時点で実際の（現在の）値を含む行を含む表を戻します。

SELECT * FROM Employee   
    FOR SYSTEM_TIME AS  OF '2016-08-06 08:32:37.91'

の間のSYSTEM_TIME そして

FOR SYSTEM_TIME FROM <start_date_time> TO <end_date_time>の説明と同じですが、返される行の表には<end_date_time>エンドポイントで定義された上限境界でアクティブになった行が含まれます。

SELECT * FROM Employee   
    FOR SYSTEM_TIME BETWEEN  '2015-01-01' AND '2015-12-31' 

FOR SYSTEM_TIME FROM に

FROM引数の<start_date_time>パラメータ値より前にアクティブになっていたか、または<end_date_time>パラメータ値の後にアクティブでなくなったかにかかわらず、指定された時間範囲内でアクティブだったすべての行バージョンの値を含むテーブルを返します。 TO引数。内部的には、一時表とその履歴表の間で結合が実行され、結果がフィルターされて、指定された時間範囲内の任意の時点でアクティブだったすべての行バージョンの値が戻されます。 FROMエンドポイントによって定義された下限境界で正確にアクティブになった行が含まれ、TOエンドポイントによって定義された上限境界で正確にアクティブになったレコードは含まれません。

SELECT * FROM Employee   
    FOR SYSTEM_TIME FROM '2015-01-01' TO '2015-12-31' 

FOR SYSTEM_TIME INに含まれる 、 ）

CONTAINED IN引数の2つの日時値によって定義された指定された時間範囲内でオープンおよびクローズされたすべての行バージョンの値を含む表を戻します。下の境界線上で正確にアクティブになった行、または上の境界線上で正確にアクティブになっていない行が含まれます。

SELECT * FROM Employee
    FOR SYSTEM_TIME CONTAINED IN ('2015-04-01', '2015-09-25')   

FOR SYSTEM_TIME ALL

現在のテーブルと履歴テーブルに属する行の和集合を返します。

SELECT * FROM Employee
    FOR SYSTEM_TIME ALL

メモリで最適化されたシステムバージョン管理された一時テーブルの作成とSQL Server履歴テーブルのクリーンアップ

デフォルトの履歴テーブルを使用して一時テーブルを作成するのは、命名を制御しながらシステムに依存して、デフォルトの構成で履歴テーブルを作成する場合に便利なオプションです。以下の例では、新しいディスクベースの履歴テーブルにリンクされた、システムでバージョン管理された新しいメモリに最適化された一時テーブルを示します。

CREATE SCHEMA History  
GO  
CREATE TABLE dbo.Department   
(  
    DepartmentNumber char(10) NOT NULL PRIMARY KEY NONCLUSTERED,   
    DepartmentName varchar(50) NOT NULL,   
    ManagerID int  NULL,   
    ParentDepartmentNumber char(10) NULL,   
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,   
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,     
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)     
)  
WITH   
    (  
        MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA,  
        SYSTEM_VERSIONING = ON ( HISTORY_TABLE = History.DepartmentHistory )   
    );  

SQL Server履歴テーブルのクリーンアップ時間の経過と共に、履歴テーブルは大幅に大きくなります。履歴テーブルのデータの挿入、更新、または削除は許可されていないため、履歴テーブルをクリーンアップする唯一の方法は、まずシステムのバージョン管理を無効にすることです。

ALTER TABLE dbo.Employee

SET（SYSTEM_VERSIONING = OFF）; GO

履歴テーブルから不要なデータを削除する：

    DELETE FROM dbo.EmployeeHistory

WHERE EndTime <= '2017-01-26 14:00:29';

システムのバージョン管理を再度有効にします。

ALTER TABLE dbo.Employee

SET（SYSTEM_VERSIONING = ON（HISTORY_TABLE = [dbo]。[EmployeeHistory]、DATA_CONSISTENCY_CHECK = ON））;

Azure SQLデータベースの履歴テーブルのクリーニングは、Azure SQLデータベースに履歴テーブルのクリーニングのサポートが組み込まれているため、少し異なります。まず、データベースレベルで一時的な履歴保持のクリーンアップを有効にする必要があります。

ALTER DATABASE CURRENT

GO TEMPORAL_HISTORY_RETENTION ON GO

次に、表ごとに保持期間を設定します。

ALTER TABLE dbo.Employee

SET（SYSTEM_VERSIONING = ON（HISTORY_RETENTION_PERIOD = 90日間））;

これにより、90日以上経過した履歴テーブル内のすべてのデータが削除されます。 SQL Server 2016のオンプレミスデータベースはTEMPORAL_HISTORY_RETENTIONとHISTORY_RETENTION_PERIODをサポートせず、上記の2つのクエリのいずれかがSQL Server 2016のオンプレミスデータベースで実行され、次のエラーが発生します。

TEMPORAL_HISTORY_RETENTIONのエラーは次のようになります。

Msg 102, Level 15, State 6, Line 34

'TEMPORAL_HISTORY_RETENTION'付近の構文が正しくありません。

HISTORY_RETENTION_PERIODの場合、エラーは次のようになります。

Msg 102, Level 15, State 1, Line 39

'HISTORY_RETENTION_PERIOD'付近の構文が正しくありません。