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

Относится к: SQL Server 2016 (13.x) и более поздние версии База данных SQL AzureУправляемый экземпляр SQL AzureSQL Database в Microsoft Fabric

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

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

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

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

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

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

Почему темпоральный?

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

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

Как работает Temporal?

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

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

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

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

Схема, показывающая, как работает темпоральная таблица.

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

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));

Дополнительные сведения см. в статье "Создание системной темпоральной таблицы".

  • Вставки: Система устанавливает значение столбца ValidFrom на начальное время текущей транзакции (в часовом поясе UTC) на основе системных часов и присваивает значение ValidTo столбца максимальному значению 9999-12-31. При этом строка помечается как открытая.

  • Обновления: система хранит предыдущее значение строки в таблице истории и устанавливает значение ValidTo столбца на начальное время текущей транзакции (в часовом поясе UTC) на основе системных часов. При этом строка помечается как закрытая с записью периода, в течение которого строка была действительной. В текущей таблице строка обновляется со своим новым значением, а система задает значение ValidFrom столбца для времени начала транзакции (в часовом поясе UTC) на основе системных часов. Значение обновлённой строки в текущей таблице для столбца ValidTo остаётся максимальным значением 9999-12-31.

  • Удаление: Система хранит предыдущее значение строки в таблице истории и устанавливает значение столбца ValidTo на начальное время текущей транзакции (в часовом поясе UTC) на основе системных часов. При этом строка помечается как закрытая с записью периода, в течение которого была действительной предыдущая строка. Строка в текущей таблице удаляется. Запросы текущей таблицы не возвращают эту строку. Только запросы, которые имеют дело с данными журнала, возвратят данные, строка для которых была закрыта.

  • Слияние: Операция ведёт себя точно так же, как если бы было выполнено до трёх операторов (INSERT, UPDATE и/или DELETE), в зависимости от того, что указано в качестве действий в операторе MERGE.

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

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

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

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

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

Диаграмма, показывающая, как работает временной запрос.

Приведенный ниже запрос ищет версии строк о сотруднике с условием фильтра 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 с JOIN, APPLY и PIVOT и Запрос данных в системно-версионной темпоральной таблице.

Expression Квалифицирование строк Note
AS OF date_time ValidFrom <= date_timeAND ValidTo >date_time Возвращает таблицу со строками, содержащими значения, которые являлись текущими в указанный момент времени в прошлом. Внутри системы объединение выполняется между темпоральной таблицей и таблицей журнала. Результаты фильтруются для возврата значений в строке, допустимой в момент времени, указанной параметром date_time . Значение строки считается допустимым, если значение system_start_time_column_name меньше или равно значению параметра date_time, а значение system_end_time_column_name больше значения параметра date_time.
FROM start_date_timeTOend_date_time ValidFrom < end_date_timeAND ValidTo >start_date_time Возвращает таблицу со значениями для всех версий строк, которые были активны в течение указанного диапазона времени, независимо от того, начали ли они активны до значения параметра start_date_time аргумента FROM или перестали быть активными после значения параметра end_date_time для аргумента TO . Внутри системы объединение выполняется между темпоральной таблицей и таблицей журнала. Результаты фильтруются, чтобы возвращать значения для всех версий строк, которые были активными в любое время в течение указанного диапазона времени. Строки, которые перестали быть активными точно на нижней границе, определенной FROM конечной точкой, не включаются, а записи, которые стали активными точно на верхней границе, определенной TO конечной точкой, также не включаются.
BETWEEN start_date_timeANDend_date_time ValidFrom <= end_date_timeAND 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_timeAND ValidTo <=end_date_time Возвращает таблицу со значениями для всех открытых и закрытых версий строк в течение указанного диапазона времени, определенного двумя значениями периода для аргумента CONTAINED IN . Включаются строки, которые стали активными ровно на нижней границе интервала или перестали быть активными ровно на верхней границе интервала.
ALL Все строки Возвращает объединение строк, принадлежащих текущей и исторической таблицам.

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

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

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

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

Samples

Связанные материалы

  • Last updated on