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

Применимо к: даSQL Server 2016 (13.x); и более поздние версии ДаБаза данных SQL Azure ДаУправляемый экземпляр SQL Azure

В SQL Server 2016 добавлена поддержка темпоральных таблиц (темпоральных таблиц с системным управлением версиями). Являясь встроенным компонентом базы данных, эти таблицы предоставляют сведения о хранящихся в них данных на любой, а не только на текущий момент времени. Темпоральные функции базы данных впервые появились в ANSI SQL 2011.

Краткое руководство

Что такое темпоральная таблица с системным управлением версиями

Темпоральная таблица с системным управлением версиями — это тип пользовательской таблицы. Она ведет журнал всех изменений данных, что позволяет легко выполнять анализ для определенной точки во времени. Этот тип темпоральной таблицы называется темпоральной таблицей с системным управлением версиями, так как периодом действия каждой строки управляет система (т. е. ядро СУБД).

В каждой темпоральной таблице есть два явно определенных столбца, каждый из которых имеет тип данных datetime2 . Эти столбцы называются столбцами периода. Эти столбцы периода используются исключительно системой для записи периода действия каждой строки при каждом изменении строки.

Наряду со столбцами периода темпоральная таблица содержит ссылку на другую таблицу с зеркальным отображением схемы. Система использует эту таблицу для автоматического сохранения предыдущей версии строки при каждом обновлении или удалении строки в темпоральной таблице. Эта дополнительная таблица называется таблицей журнала, а главная таблица, в которой хранятся текущие (фактические) версии строк, называется текущей таблицей или просто темпоральной таблицей. Во время создания темпоральной таблицы можно указать существующую таблицу журнала (она должна соответствовать схеме) или позволить системе создать таблицу журнала по умолчанию.

Зачем нужны темпоральные таблицы

Реальные источники данных являются динамическими, и в бизнес-решениях чаще всего используются сведения, которые аналитики могут получить из развития данных. Варианты использования темпоральных таблиц включают следующее.

  • Аудит всех изменений данных и выполнение экспертизы данных при необходимости.
  • Восстановление состояния данных на любой момент времени в прошлом.
  • Вычисление тенденций во времени.
  • Поддержка медленно изменяющегося измерения для приложений, связанных с поддержкой принятия решений.
  • Восстановление после случайных данных изменений и ошибок приложений.

Как работают темпоральные таблицы

Системное управление версиями для таблицы реализовано в виде пары таблиц — текущей таблицы и таблицы журнала. В каждой из этих таблиц используются следующие два дополнительных столбца datetime2 для определения периода действия для каждой записи.

  • Столбец начала периода: в этот столбец система записывает время начала действия для строки; обычно этот столбец обозначается как SysStartTime.
  • Столбец окончания периода: в этот столбец система записывает время окончания действия для строки; обычно этот столбец обозначается как SysEndTime.

В текущей таблице содержится текущее значение для каждой строки. В таблице журнала содержатся все предыдущие значения для каждой строки и время начала и время окончания периода, в котором действовали эти значения (если они заданы).

Схема работы темпоральной таблицы.

В следующем простом примере показан сценарий с информацией в таблице Employee в гипотетической базе данных HR.

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));
  • Вставки: в INSERT система устанавливает значение в столбце SysStartTime (в примере ему задано имя ValidFrom) в соответствии с временем начала текущей транзакции (в часовом поясе UTC) в зависимости от системных часов и устанавливает для столбца SysEndTime (в примере ему задано имя ValidTo) максимальное значение 9999-12-31. При этом строка помечается как открытая.
  • Обновления: в UPDATE система сохраняет предыдущее значение строки в таблице журнала и устанавливает значение для столбца SysEndTime в соответствии с временем начала текущей транзакции (в часовом поясе UTC) в зависимости от системных часов. При этом строка помечается как закрытая с записью периода, в течение которого строка была действительной. В текущей таблице строка обновляется в соответствии с новым значением, и система устанавливает значение для столбца SysStartTime в соответствии с временем начала текущей транзакции (в часовом поясе UTC) в зависимости от системных часов. Значение в столбце SysEndTime для обновленной строки в текущей таблице остается максимальным: 9999-12-31.
  • Удаления: в DELETE система сохраняет предыдущее значение строки в таблице журнала и устанавливает значение для столбца SysEndTime в соответствии с временем начала текущей транзакции (в часовом поясе UTC) в зависимости от системных часов. При этом строка помечается как закрытая с записью периода, в течение которого была действительной предыдущая строка. Строка в текущей таблице удаляется. Запросы к текущей таблице не будут возвращать эту строку. Только запросы, которые имеют дело с данными журнала, возвратят данные, строка для которых была закрыта.
  • Слияние: в MERGE операция выполняется так же, как если бы выполнялись три инструкции ( INSERT, UPDATE и (или) DELETE), в зависимости от того, что указано в качестве действий в инструкции MERGE.

Важно!

Время, записанное в системных столбцах datetime2, основано на времени начала выполнения самой транзакции. Например, все строки, которые вставлены в рамках одной транзакции, будут иметь одно и то же время UTC, записанное в столбец, соответствующий началу периода SYSTEM_TIME .

Как выполнить запрос для темпоральных данных

Инструкция SELECT для предложения FROM <table> имеет новое предложение FOR SYSTEM_TIME с пятью вложенными предложениями для темпоральных таблиц, которое позволяет запрашивать данные в текущей таблице и в таблице журнала. Этот новый синтаксис инструкции SELECT поддерживается непосредственно для одной таблицы, распространяется через несколько соединений и представлений на основе нескольких темпоральных таблиц.

Схема работы темпорального запроса.

Приведенный ниже запрос ищет версии строки о сотруднике с EmployeeID = 1000, которые были активны по крайней мере часть времени между 1 января 2014 г. и 1 января 2015 г. (включая верхнюю границу периода).

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). Эти строки будут созданы, если выполнить несколько изменений одного и того же первичного ключа в одной и той же транзакции. В этом случае темпоральные запросы возвращают только версии строк до выполнения транзакций и версии строк, которые становятся действительными после выполнения транзакций. Если необходимо включить эти строки в анализ, выполните запрос к таблице журнала напрямую.

В таблице ниже SysStartTime в столбце "Подходящие строки" представляет значение в столбце SysStartTime опрашиваемой таблицы, а SysEndTime представляет значение в столбце SysEndTime опрашиваемой таблицы. Полный синтаксис и примеры см. в разделах FROM (Transact-SQL) и Запрос данных в темпоральной таблице с системным управлением версиями.

Выражение Подходящие строки Описание
AS OF<дата_время> SysStartTime <= date_time AND SysEndTime > date_time Возвращает таблицу со строками, содержащими значения, которые были актуальными (текущими) в указанный момент времени в прошлом. На внутреннем уровне объединение выполняется между темпоральной таблицей и соответствующей таблицей журнала, и результаты отфильтровываются так, чтобы возвращались значения в строке, которая была действительной на момент времени, определяемый параметром <дата_время> . Значение для строки считается действительным, если значение system_start_time_column_name меньше или равно значению параметра <дата_время> , а значение system_end_time_column_name больше значения параметра <дата_время> .
FROM<дата_время_начала>TO<дата_время_окончания> SysStartTime < дата_время_окончания AND SysEndTime > дата_время_начала Возвращает таблицу, содержащую значения для всех версий строк, которые были активны в течение указанного интервала времени независимо от того, стали ли они активными до значения параметра <дата_время_начала> аргумента FROM или перестали быть активными после значения параметра <дата_время_окончания> аргумента TO. На внутреннем уровне объединение выполняется между темпоральной таблицей и соответствующей таблицей журнала и результаты отфильтровываются так, чтобы возвращать значения для всех версий строк, которые были активными в течение указанного временного диапазона. Сюда не включаются те строки, которые перестали быть активными точно в нижнюю границу периода времени, определяемую конечной точкой FROM, а также те, которые стали активными точно в верхнюю границу периода времени, определяемую конечной точкой TO.
BETWEEN<дата_время_начала>AND<дата_время_окончания> SysStartTime <= end_date_time AND SysEndTime > start_date_time Аналогично приведенному выше описанию для FOR SYSTEM_TIME FROM <дата_время_начала>TO <дата_время_окончания> за исключением того, что таблица возвращаемых строк включает строки, которые стали активными точно в верхнюю границу периода времени, определяемую конечной точкой <дата_время_окончания>.
CONTAINED IN (<дата_время_начала> , <дата_время_окончания>) SysStartTime >= дата_время_начала AND SysEndTime <= дата_время_окончания Возвращает таблицу, содержащую значения для всех версий строк, которые были открыты и закрыты в течение указанного интервала времени, определяемого двумя значениями даты и времени в аргументе CONTAINED IN. В эти строки включаются те, которые стали активными точно в нижнюю границу периода времени, и те, которые перестали быть активными точно в верхнюю границу периода времени.
ALL Все строки Возвращает объединение строк, принадлежащих текущей таблице и таблице журнала.

Примечание

При желании можно скрыть эти столбцы периодов, чтобы запросы, в которых они не указаны явно, не возвращали их (сценарий SELECT * FROM <table> ). Чтобы вернуть скрытый столбец, просто явно укажите его в запросе. Аналогичным образом продолжат работать инструкции INSERT и BULK INSERT , как если бы эти новые столбцы периодов отсутствовали (значения столбцов будут заполнены автоматически). Дополнительные сведения об использовании предложения HIDDEN см. в разделах CREATE TABLE (Transact-SQL) и ALTER TABLE (Transact-SQL).

Дальнейшие действия