Поделиться через

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

  • Статья

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

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

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

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

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

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

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

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

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

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

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

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

  • Слияние: операция ведет себя точно так же, как если бы в инструкции выполнялось до трех операторов (аUPDATEINSERT, а, или или) 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 Квалифицирование строк Примечание.
AS OFdate_time ValidFrom <= AND ValidTo > date_time date_time Возвращает таблицу со строками, содержащими значения, которые являлись текущими в указанный момент времени в прошлом. Внутри системы объединение выполняется между темпоральной таблицей и таблицей журнала. Результаты фильтруются для возврата значений в строке, допустимой в момент времени, указанной параметром date_time . Значение строки считается допустимым, если значение system_start_time_column_name меньше или равно значению параметра date_time, а значение system_end_time_column_name больше значения параметра date_time.
FROM TO start_date_time end_date_time ValidFrom < AND ValidTo > end_date_time start_date_time Возвращает таблицу со значениями для всех версий строк, которые были активны в течение указанного диапазона времени, независимо от того, начали ли они активны до значения параметра start_date_time аргумента FROM или перестали быть активными после значения параметра end_date_time для аргумента TO . Внутри системы объединение выполняется между темпоральной таблицей и таблицей журнала. Результаты фильтруются, чтобы возвращать значения для всех версий строк, которые были активными в любое время в течение указанного диапазона времени. Строки, которые перестали быть активными точно на нижней границе, определенной FROM конечной точкой, не включаются, а записи, которые стали активными точно на верхней границе, определенной TO конечной точкой, также не включаются.
BETWEEN AND start_date_time end_date_time ValidFrom <= AND ValidTo > end_date_time start_date_time Аналогично описанию FOR SYSTEM_TIME FROM start_date_time end_date_timeTO, кроме таблицы возвращаемых строк, содержатся строки, которые стали активными на верхней границе, определенной конечной точкой end_date_time.
CONTAINED IN(start_date_time, end_date_time) ValidFrom >= AND ValidTo <= start_date_time end_date_time Возвращает таблицу со значениями для всех открытых и закрытых версий строк в течение указанного диапазона времени, определенного двумя значениями периода для аргумента CONTAINED IN . В эти строки включаются те, которые стали активными точно в нижнюю границу периода времени, и те, которые перестали быть активными точно в верхнюю границу периода времени.
ALL Все строки Возвращает объединение строк, принадлежащих текущей таблице и таблице журнала.

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

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

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

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

Примеры

  • ASP.NET. Сведения о создании темпорального приложения с помощью темпоральных таблиц см. в веб-приложении ASP.NET Core.

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

Связанный контент