テンポラル テーブルTemporal tables

適用対象: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL Server 2016 では、現時点の正しいデータのみではなく、テーブルに保存されていた任意の時点のデータ情報を提供することを組み込みでサポートするデータベース機能として、テンポラル テーブル (システム バージョン管理されたテンポラル テーブルとも呼ばれています) が導入されました。SQL Server 2016 introduced support for temporal tables (also known as system-versioned temporal tables) as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. テンポラルは、ANSI SQL 2011 で導入されたデータベース機能です。Temporal is a database feature that was introduced in ANSI SQL 2011.

クイックスタートQuick-start:

システム バージョン管理されたテンポラル テーブルとはWhat is a system-versioned temporal table?

システム バージョン管理されたテンポラル テーブルは、データ変更の履歴を完全に保持し、特定の時点の分析を簡単に実行できるよう設計されたユーザー テーブルの一種です。A system-versioned temporal table is a type of user table designed to keep a full history of data changes and allow easy point in time analysis. 各行の有効期間はシステム (つまりデータベース エンジン) によって管理されているため、この種類のテンポラル テーブルは、システム バージョン管理されたテンポラル テーブルと呼ばれています。This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine).

すべてのテンポラル テーブルには、それぞれに datetime2 データ型が明示的に定義されている 2 つの列があります。Every temporal table has two explicitly defined columns, each with a datetime2 data type. これらの列は、期間列と呼ばれます。These columns are referred to as period columns. これら期間列は、行が変更されるたびに各行の有効期間を記録するためにシステムのみに使用されます。These period columns are used exclusively by the system to record period of validity for each row whenever a row is modified.

テンポラル テーブルには、これらの期間列に加え、ミラー化されたスキーマを使用する別のテーブルへの参照も含まれています。In addition to these period columns, a temporal table also contains a reference to another table with a mirrored schema. システムでは、このテーブルを使用して、テンポラル テーブルの行が更新または削除されるたびに、行の以前のバージョンを自動的に保存します。The system uses this table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted. 現在 (実際) の行バージョンを保存するメインのテーブルが現行テーブルまたはテンポラル テーブルと簡単に呼ばれているのに対し、この追加のテーブルは、履歴テーブルと呼ばれています。This additional table is referred to as the history table, while the main table that stores current (actual) row versions is referred to as the current table or simply as the temporal table. テンポラル テーブルの作成時、ユーザーは既存の履歴テーブルを指定するか (スキーマ準拠である必要があります)、システムに既定の履歴テーブルを作成させます。During temporal table creation users can specify existing history table (must be schema compliant) or let system create default history table.

テンポラルである理由Why temporal?

データの実際のソースは動的で、ビジネスの意思決定はアナリストがデータの進化から得ることができる洞察に通常依存しています。Real data sources are dynamic and more often than not business decisions rely on insights that analysts can get from data evolution. テンポラル テーブルの使用例は次のとおりです。Use cases for temporal tables include:

  • すべてのデータ変更の監査と、必要に応じてのデータの科学捜査の実行Auditing all data changes and performing data forensics when necessary
  • 過去の任意の時点でのデータの状態の再構築Reconstructing state of the data as of any time in the past
  • 長期の傾向の計算Calculating trends over time
  • 意思決定支援アプリケーションのための緩やかに変化するディメンションの維持Maintaining a slowly changing dimension for decision support applications
  • 偶発的なデータ変更やアプリケーション エラーからの復旧Recovering from accidental data changes and application errors

テンポラルのしくみHow does temporal work?

テーブルのシステム バージョン管理は、現行テーブルと履歴テーブルの 1 組のテーブルとして実装されます。System-versioning for a table is implemented as a pair of tables, a current table and a history table. これらの各テーブル内には、次の 2 つの追加の datetime2 列で、各行の有効期間が定義されています。Within each of these tables, the following two additional datetime2 columns are used to define the period of validity for each row:

  • 期間開始列: システムにより、この行の開始時間が、通常は SysStartTime 列である列に書き込まれます。Period start column: The system records the start time for the row in this column, typically denoted as the SysStartTime column.
  • 期間終了列: システムにより、この行の終了時間が、通常は SysEndTime 列である列に書き込まれます。Period end column: The system records the end time for the row in this column, typically denoted as the SysEndTime column.

現行テーブルには、各行の現在の値が含まれています。The current table contains the current value for each row. 履歴テーブルには、存在する場合、各行のそれぞれの以前の値と、それが有効であった期間の開始時間と終了時間が含まれています。The history table contains each previous value for each row, if any, and the start time and end time for the period for which it was valid.

テンポラルのしくみTemporal-HowWorks

次に、Employee 情報が仮定の HR データベースにあるシナリオの簡単な例を示します。The following simple example illustrates a scenario with Employee information in hypothetical HR database:

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 GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
  • INSERTS: システムにより、INSERT では、SysStartTime 列には、システム クロックに基づく現在のトランザクションの開始時間 (UTC タイム ゾーン) の値が設定され、SysEndTime 列には、最大値の 9999-12-31 の値が割り当てられます。 INSERTS: On an INSERT, the system sets the value for the SysStartTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock and assigns the value for the SysEndTime column to the maximum value of 9999-12-31. これは行をオープンとマークします。This marks the row as open.
  • UPDATES: システムにより、UPDATE では、行の前の値が履歴テーブルに保存され、SysEndTime 列には、システム クロックに基づく現在のトランザクションの開始時間 (UTC タイム ゾーン) の値が設定されます。 UPDATES: On an UPDATE, the system stores the previous value of the row in the history table and sets the value for the SysEndTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock. これは行をクローズドとマークし、行が有効であった期間が記録されます。This marks the row as closed, with a period recorded for which the row was valid. 現行テーブルでは、行は新しい値で更新され、システムにより SysStartTime 列には、システム クロックに基づくトランザクションの開始時間 (UTC タイム ゾーン) の値が設定されます。In the current table, the row is updated with its new value and the system sets the value for the SysStartTime column to the begin time for the transaction (in the UTC time zone) based on the system clock. 現行テーブル内の SysEndTime 列の更新された行の値は、最大値の 9999-12-31 のままです。The value for the updated row in the current table for the SysEndTime column remains the maximum value of 9999-12-31.
  • DELETES: システムにより、DELETE では、行の前の値が履歴テーブルに保存され、SysEndTime 列には、システム クロックに基づく現在のトランザクションの開始時間 (UTC タイム ゾーン) の値が設定されます。 DELETES: On a DELETE, the system stores the previous value of the row in the history table and sets the value for the SysEndTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock. これは行をクローズドとマークし、前の行が有効であった期間が記録されます。This marks the row as closed, with a period recorded for which the previous row was valid. 現行テーブルでは、その行は削除されます。In the current table, the row is removed. 現行テーブルに対するクエリでは、この行は返されません。Queries of the current table will not return this row. 履歴データを処理するクエリのみで、クローズドの行のデータが返されます。Only queries that deal with history data return data for which a row is closed.
  • MERGE: MERGE では、MERGE ステートメントでアクションとして指定されている内容に応じて、まさに最大 3 つのステートメント (INSERTUPDATE、または DELETE、あるいはこれらすべて) が実行されたかのような動作になります。 MERGE: On a MERGE, the operation behaves exactly as if up to three statements (an INSERT, an UPDATE, and/or a DELETE) executed, depending on what is specified as actions in the MERGE statement.

重要

システム datetime2 列に記録されている時間は、トランザクション自体の開始時間に基づいています。The times recorded in the system datetime2 columns are based on the begin time of the transaction itself. たとえば、1 つのトランザクションで挿入されたすべての行の、 SYSTEM_TIME 期間の開始に対応する列の UTC 時間は同じになります。For example, all rows inserted within a single transaction will have the same UTC time recorded in the column corresponding to the start of the SYSTEM_TIME period.

テンポラル データのクエリ方法How do I query temporal data?

SELECT ステートメントの FROM <table> 句には、現行および履歴テーブルのデータをクエリする新しい FOR SYSTEM_TIME 句が導入されました。これには、テンポラル専用のサブ句が 5 つあります。The SELECT statement FROM<table> clause has a new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses to query data across the current and history tables. この新しい SELECT ステートメントの構文は、1 つのテーブルで直接サポートされており、複数の結合を介して、また複数のテンポラル テーブル上のビューを介して反映されます。This new SELECT statement syntax is supported directly on a single table, propagated through multiple joins, and through views on top of multiple temporal tables.

テンポラルのクエリTemporal-Querying

次のクエリでは、少なくとも 2014 年の 1 月 1 日から 2015 年 1 月 1 日 (上限の境界を含む) の間にアクティブであった、EmployeeID が 1000 の従業員行の行バージョンが検索されます。The following query searches for row versions for Employee row with EmployeeID = 1000 that were active at least for a portion of period between 1st January of 2014 and 1st January 2015 (including the upper boundary):

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 では、有効時間がゼロの行は除外されます (SysStartTime = SysEndTime)。 FOR SYSTEM_TIME filters out rows that have period of validity with zero duration (SysStartTime SysEndTime). それらの行は、同じトランザクションで同じ主キーに対して複数の更新を実行すると生成されます。Those rows will be generated if you perform multiple updates on the same primary key within the same transaction. その場合は、テンポラル クエリでは、トランザクション前と、トランザクション後に現行となったの行番号のみを示します。In that case, temporal querying surfaces only row versions before the transactions and ones that became actual after the transactions. それらの行を分析に含める必要がある場合は、履歴テーブルを直接クエリします。If you need to include those rows in the analysis, query the history table directly.

次の表の該当行列の SysStartTime は、クエリ対象のテーブルの SysStartTime 列の値で、 SysEndTime はクエリ対象のテーブルの SysEndTime 列の値です。In the table below, SysStartTime in the Qualifying Rows column represents the value in the SysStartTime column in the table being queried and SysEndTime represents the value in the SysEndTime column in the table being queried. 完全な構文と例については、「 FROM (Transact-SQL) 」、および「 システム バージョン管理されたテンポラル テーブルのデータのクエリでサポートされているデータベース機能です。For the full syntax and for examples, see FROM (Transact-SQL) and Querying Data in a System-Versioned Temporal Table.

Expression 該当行Qualifying Rows [説明]Description
AS OF<date_time>
AS OF<date_time> SysStartTime <= date_time AND SysEndTime > date_timeSysStartTime <= date_time AND SysEndTime > date_time 過去の指定時点の実際 (現行) の値を含む行のあるテーブルを返します。Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past. 内部的には、テンポラル テーブルとその履歴テーブルの結合が行われ、結果がフィルター処理されて、 <date_time> パラメーターで指定された特定の時点で有効だった行の値が返されます。Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values in the row that was valid at the point in time specified by the <date_time> parameter. system_start_time_column_name 値が <date_time> パラメーター値と等しいかそれよりも小さく、system_end_time_column_name 値が <date_time> パラメーター値より大きい場合に、行の値は有効と見なされます。The value for a row is deemed valid if the system_start_time_column_name value is less than or equal to the <date_time> parameter value and the system_end_time_column_name value is greater than the <date_time> parameter value.
FROM<start_date_time>TO<end_date_time>
FROM<start_date_time>TO<end_date_time> SysStartTime < end_date_time AND SysEndTime > start_date_timeSysStartTime < end_date_time AND SysEndTime > start_date_time 指定した時間範囲内でアクティブだったすべての行バージョンの値を含むテーブルを返します。FROM 引数の <start_date_time> パラメーター値の前にアクティブになったか、TO 引数の <end_date_time> パラメーター値の後にアクティブでなくなったかに無関係です。Returns a table with the values for all row versions that were active within the specified time range, regardless of whether they started being active before the <start_date_time> parameter value for the FROM argument or ceased being active after the <end_date_time> parameter value for the TO argument. 内部的には、共用体が一時的なテーブルとその履歴テーブルの間実行され、結果をフィルター処理すると、指定した時間範囲の中にいつでもにアクティブだったすべての行のバージョンの値を返します。Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values for all row versions that were active at any time during the time range specified. FROM エンドポイントによって定義されている下限のちょうど境界上でアクティブではなくなった行は含まれず、TO エンドポイントによって定義された上限のちょうど境界上でアクティブになったレコードも含まれません。Rows that ceased being active exactly on the lower boundary defined by the FROM endpoint are not included and records that became active exactly on the upper boundary defined by the TO endpoint are not included also.
BETWEEN<start_date_time>AND<end_date_time>
BETWEEN<start_date_time>AND<end_date_time> SysStartTime <= end_date_time AND SysEndTime > start_date_timeSysStartTime <= end_date_time AND SysEndTime > start_date_time 返される行のテーブルには <end_date_time> エンドポイントで定義された上限の境界でアクティブになった行が含まれることを除き、FOR SYSTEM_TIME FROM <start_date_time>TO <end_date_time> の説明と同じです。Same as above in the FOR SYSTEM_TIME FROM <start_date_time>TO <end_date_time> description, except the table of rows returned includes rows that became active on the upper boundary defined by the <end_date_time> endpoint.
CONTAINED IN (<start_date_time> , <end_date_time>)
CONTAINED IN (<start_date_time> , <end_date_time>) SysStartTime >= start_date_time AND SysEndTime <= end_date_timeSysStartTime >= start_date_time AND SysEndTime <= end_date_time CONTAINED IN 引数の 2 つの datetime 値で定義された指定時間範囲内に開かれて閉じられたすべての行バージョンの値を含むテーブルを返します。Returns a table with the values for all row versions that were opened and closed within the specified time range defined by the two datetime values for the CONTAINED IN argument. 行が下位の境界に正確に有効になったまたは上限の境界上だけでアクティブにされているが中断されることでは、含まれています。Rows that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included.
ALLAll すべての行All rows 現行および履歴テーブルに属する行の和を返します。Returns the union of rows that belong to the current and the history table.

注意

必要に応じて、これらの期間列を明示的に参照しないクエリがこれらの列を返さないよう、これらの期間列を隠すこともできます (SELECT * FROM <table> シナリオ)。Optionally, you can choose to hide these period columns such that queries that do not explicitly reference these period columns do not return these columns (the SELECT FROM table scenario). 非表示の列を返すには、クエリで非表示の列を単純に明示的に参照してください。To return a hidden column, simply explicitly refer to the hidden column in the query. 同様に、 INSERT および BULK INSERT ステートメントでも、これらの新しい期間列が存在しないかのように続行されます (そして列値は自動入力されます)。Similarly INSERT and BULK INSERT statements will continue as if these new period columns were not present (and the column values will be auto-populated). HIDDEN 句の使用方法の詳細については、「CREATE TABLE (Transact-SQL)」と「ALTER TABLE (Transact-SQL)」を参照してください。For details on using the HIDDEN clause, see CREATE TABLE (Transact-SQL) and ALTER TABLE (Transact-SQL).

次のステップNext steps