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

  • Статья

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

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

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

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

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

В каждой темпоральной таблице есть два явно определенных столбца, каждый из которых имеет тип данных 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));

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

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

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) и запрос данных в системной темпоральной таблице.

Expression Подходящие строки Примечание.
AS OFdate_time ValidFrom <=AND ValidTo >date_time date_time Возвращает таблицу со строками, содержащими значения, которые являлись текущими в указанный момент времени в прошлом. На внутреннем уровне объединение выполняется между темпоральной таблицей и соответствующей таблицей журнала, и результаты отфильтровываются так, чтобы возвращались значения в строке, которая была действительной на момент времени, определяемый параметром дата_время. Значение для строки считается действительным, если значение system_start_time_column_name меньше или равно значению параметра дата_время, а значение system_end_time_column_name больше значения параметра дата_время.
FROMTOstart_date_time end_date_time ValidFrom <AND ValidTo >end_date_time start_date_time Возвращает таблицу со значениями для всех версий строк, которые были активны в течение указанного диапазона времени, независимо от того, начали ли они активны до значения параметра start_date_time аргумента FROM или перестали быть активными после значения параметра end_date_time для аргумента TO . Внутри системы объединение выполняется между темпоральной таблицей и таблицей журнала. Результаты фильтруются, чтобы возвращать значения для всех версий строк, которые были активными в любое время в течение указанного диапазона времени. Строки, которые перестали быть активными точно на нижней границе, определенной FROM конечной точкой, не включаются, а записи, которые стали активными точно на верхней границе, определенной TO конечной точкой, также не включаются.
BETWEENANDstart_date_time end_date_time ValidFrom <=AND ValidTo >end_date_time start_date_time Аналогично предыдущему описанию FOR SYSTEM_TIME FROMstart_date_timeTOend_date_time, кроме таблицы возвращаемых строк, содержатся строки, которые стали активными на верхней границе, определенной конечной точкой 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.

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

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

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