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

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

Темпоральные таблицы (также известные, как темпоральные таблицы с системным управлением версиями) являются компонентом базы данных, который позволяет предоставлять данные, хранящиеся в таблице, в любой момент времени, а не только в текущий.

Ознакомьтесь со статьями Начало работы с темпоральными таблицами с системным управлением версиями и Сценарии использования темпоральных таблиц.

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

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

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

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

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

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

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

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

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

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

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

Diagram showing how a Temporal table works

В следующем скрипте описан сценарий с данными сотрудника:

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

Важно!

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

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

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

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

Diagram showing how Temporal Querying works

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

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

Примечание

FOR SYSTEM_TIME фильтрует строки, которые имеют срок действия с нулевой продолжительностью (ValidFrom = ValidTo).

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

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

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

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

Как скрыть столбцы периода

Можно скрыть столбцы периода, чтобы запросы, которые на них не ссылаются явно, не возвращали эти столбцы (например, при выполнении SELECT * FROM <table>).

Чтобы вернуть скрытый столбец, явно укажите его в запросе. Аналогичным образом продолжат работать инструкции INSERT и BULK INSERT, как будто новые столбцы периода отсутствуют (значения столбцов будут заполнены автоматически).

Дополнительные сведения об использовании предложения HIDDEN см. в разделах CREATE TABLE и ALTER TABLE.

Примеры

ASP.NET

Чтобы научиться создавать темпоральное приложение с помощью темпоральных таблиц, см. ссылку веб-приложение ASP.NET Core.

Скачайте пример базы данных Adventure Works

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

См. также

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