CRUD-операции с темпоральными таблицами MSSQL

Простое сохранение и доступ к историческим данным в SQL Server

Темпоральные таблицы (или таблицы с системным управлением версиями) в MSSQL позволяют автоматически сохранять и запрашивать исторические данные. В этом руководстве я буду использовать SSMS и локальный экземпляр бесплатной SQL Server Developer Edition.

Создание базы данных

Я создал базу данных, в которой будут храниться данные клиентов фиктивной службы подписки на видео под названием VideoSub с помощью этой строки:

CREATE DATABASE VideoSub

Добавление темпоральной таблицы

Теперь, когда у нас есть место для хранения нашей таблицы, мы можем создать ее с помощью пункта «Новый запрос» или пункта меню «Таблица с системной версией». Если по какой-то причине вы не видите панель баз данных (обозреватель объектов), вы можете найти ее, нажав F8.

Темпоральной таблице необходимы:

ПЕРВИЧНЫЙ ключ
Столбец типа datetime2 с оператором GENERATED ALWAYS AS ROW START
Другой тип datetime2 с GENERATED ALWAYS AS ROW END. В этом поле будет указана дата, когда значение стало историческим.
Столбец PERIOD, в котором указаны даты для фильтрации при запросе таблиц.

Когда мы создаем темпоральную таблицу, SQL создает две таблицы: временную и таблицу истории. Вот сценарий для нашей новой таблицы:

Если вы опустите HISTORY_TABLE =…, ваша таблица истории будет по умолчанию называться примерно так: MSSQL_TemporalHistoryFor_12345678.

Наша таблица должна выглядеть так:

Вставка

INSERT INTO dbo.Customer (FirstName, LastName) VALUES ('John', 'Smith')

Это только добавит запись во временную таблицу, но не в историю. Посмотрим на наши результаты.

Если вы следуете:

SELECT * FROM VideoSub.dbo.Customer
SELECT * FROM VideoSub.dbo.CustomerHistory

Обновления

Давайте добавим телефон нашему клиенту Джону.

UPDATE dbo.Customer set Phone = ‘555–5555’
WHERE ID = 1

При обновлении в таблицу истории добавляется запись (запись, в которой не было телефона)
Устанавливает ValidTo в dbo.CustomerHistory на момент обновления.
ValidFrom в dbo.Customer равно ValidTo в таблице истории.

Запрос

Допустим, вы хотите увидеть, как таблица выглядела в определенную дату и время. Чтобы увидеть этот эффект и «путешествие во времени», давайте сначала добавим запись в нашу таблицу.

INSERT INTO dbo.Customer (FirstName, LastName) VALUES (‘Jane’, ‘Smith’)

Теперь у нас есть 2 клиента, давайте запустим следующий запрос и посмотрим, что произойдет:

SELECT * FROM VideoSub.dbo.Customer
FOR SYSTEM_TIME AS OF <any ValidTo date>

Сравнивая последний запрос со всеми существующими клиентами, мы можем проверить это:

Удаление

Теперь давайте изменим некоторые данные в нашем первом клиенте, а затем удалим их:

UPDATE customer SET FirstName = ‘johno’
WHERE ID = 1
DELETE customer WHERE id = 1

Полную историю клиента, который был удален, можно найти с помощью этого запроса:

SELECT ch.*
FROM CustomerHistory ch
LEFT JOIN Customer c ON c.ID = ch.ID
WHERE ch.ID = 1 AND c.ID IS NULL

Усечь (невозможно)

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