Темпоральные таблицыTemporal Tables

ОБЛАСТЬ ПРИМЕНЕНИЯ:даSQL Server (начиная с 2016)даБаза данных SQL AzureдаХранилище данных SQL AzureнетParallel Data WarehouseAPPLIES TO: yesSQL Server (starting with 2016) yesAzure SQL Database noAzure SQL Data Warehouse 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 .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?

Системное управление версиями для таблицы реализовано в виде пары таблиц — текущей таблицы и таблицы журнала.System-versioning for a table is implemented as a pair of tables, a current table and a history table. В каждой из этих таблиц используются следующие два дополнительных столбца 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 (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 система устанавливает значение в столбце 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.

Обновления: в 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.

Удаления: в 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операция выполняется так же, как если бы выполнялись три инструкции ( INSERT, UPDATE и (или) DELETE), в зависимости от того, что указано в качестве действий в инструкции MERGE .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. Например, все строки, которые вставлены в рамках одной транзакции, будут иметь одно и то же время UTC, записанное в столбец, соответствующий началу периода SYSTEM_TIME .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 <таблица> имеет новое предложение FOR SYSTEM_TIME с пятью вложенными предложениями для темпоральных таблиц, которое позволяет запрашивать данные из текущих таблиц и из таблиц журнала.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 поддерживается непосредственно для одной таблицы, распространяется через несколько соединений и представлений на основе нескольких темпоральных таблиц.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

Приведенный ниже запрос ищет версии строки о сотруднике с EmployeeID = 1000, которые были активны по крайней мере часть времени между 1 января 2014 г. и 1 января 2015 г. (включая верхнюю границу периода).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<дата_время>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. На внутреннем уровне объединение выполняется между темпоральной таблицей и соответствующей таблицей журнала, и результаты отфильтровываются так, чтобы возвращались значения в строке, которая была действительной на момент времени, определяемый параметром <дата_время> .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 меньше или равно значению параметра <дата_время> , а значение system_end_time_column_name больше значения параметра <дата_время> .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<дата_время_начала>TO<дата_время_окончания>FROM<start_date_time>TO<end_date_time> SysStartTime < дата_время_окончания AND SysEndTime > дата_время_началаSysStartTime < end_date_time AND SysEndTime > start_date_time Возвращает таблицу, содержащую значения для всех версий строк, которые были активны в течение указанного интервала времени независимо от того, стали ли они активными до значения параметра <дата_время_начала> аргумента FROM или перестали быть активными после значения параметра <дата_время_окончания> аргумента TO.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<дата_время_начала>AND<дата_время_окончания>BETWEEN<start_date_time>AND<end_date_time> SysStartTime <= дата_время_начала AND SysEndTime > дата_время_окончанияSysStartTime <= end_date_time AND SysEndTime > start_date_time Аналогично приведенному выше описанию для FOR SYSTEM_TIME FROM <дата_время_начала>TO <дата_время_окончания> за исключением того, что таблица возвращаемых строк включает строки, которые стали активными точно в верхнюю границу периода времени, определяемую конечной точкой <дата_время_окончания>.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 (<дата_время_начала> , <дата_время_окончания>)CONTAINED IN (<start_date_time> , <end_date_time>) SysStartTime >= дата_время_начала AND SysEndTime <= дата_время_окончанияSysStartTime >= start_date_time AND SysEndTime <= end_date_time Возвращает таблицу, содержащую значения для всех версий строк, которые были открыты и закрыты в течение указанного интервала времени, определяемого двумя значениями даты и времени в аргументе CONTAINED IN.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 <таблица> ).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).

См. также:See Also

Приступая к работе c темпоральными таблицами с системным управлением версиями Getting Started with System-Versioned Temporal Tables
Темпоральные таблицы с системным управлением версиями и таблицы, оптимизированные для памяти System-Versioned Temporal Tables with Memory-Optimized Tables
Сценарии использования темпоральных таблиц Temporal Table Usage Scenarios
Рекомендации и ограничения для темпоральной таблицы Temporal Table Considerations and Limitations
Управление хранением данных журнала в темпоральных таблицах с системным управлением версиями Manage Retention of Historical Data in System-Versioned Temporal Tables
Секционирование с помощью темпоральных таблиц Partitioning with Temporal Tables
Проверка согласованности системной темпоральной таблицы Temporal Table System Consistency Checks
Безопасность темпоральных таблиц Temporal Table Security
Представления и функции метаданных для временной таблицыTemporal Table Metadata Views and Functions