Temporal Tables в MS SQL Server: история изменений / Хабр

Привет, Хабр!

Temporal tables позволяют следить за историями изменений уровне движка. SQL Server сам хранит полную историю изменений каждой строки — без триггеров, без дополнительного кода и без самописного аудита. Фича появилась в SQL Server 2016 и к сегодняшнему дню обросла возможностями. Разберём, как все устроено и как использовать.

Идея: каждая строка знает, когда она была актуальна

Temporal table — обычная таблица с двумя дополнительными столбцами: начало и конец периода актуальности строки. Плюс связанная таблица истории, куда SQL Server автоматически складывает предыдущие версии строк при UPDATE и DELETE.

CREATE TABLE dbo.Products
(
    ProductId    INT PRIMARY KEY,
    Name         NVARCHAR(200) NOT NULL,
    Price        DECIMAL(18,2) NOT NULL,
    Discount     INT NOT NULL DEFAULT 0,
    
    -- Период актуальности (обязательно для temporal)
    ValidFrom    DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo      DATETIME2 GENERATED ALWAYS AS ROW END   NOT NULL,
    
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.ProductsHistory
    )
);

ValidFrom и ValidTo — системные столбцы, которые SQL Server заполняет автоматически. ValidFrom — момент, когда строка стала актуальной. ValidTo — момент, когда она перестала быть актуальной. Для текущих строк ValidTo = 9999-12-31 23:59:59.

HISTORY_TABLE — куда складывать историю. Если не указать — SQL Server создаст таблицу с автоматическим именем.

Как это работает

INSERT — ValidFrom = текущее время UTC, ValidTo = максимум.

UPDATE — текущая строка копируется в таблицу истории (с ValidTo = текущее время). В основной таблице строка обновляется, ValidFrom = текущее время.

DELETE — строка перемещается в таблицу истории с ValidTo = текущее время. Из основной удаляется.

Обычные DML‑запросы работают как раньше:

-- Создаём товар
INSERT INTO dbo.Products (ProductId, Name, Price, Discount)
VALUES (1, N'Ноутбук', 75000.00, 0);

-- Меняем цену
UPDATE dbo.Products SET Price = 69900.00 WHERE ProductId = 1;

-- Добавляем скидку
UPDATE dbo.Products SET Discount = 15 WHERE ProductId = 1;

-- Снижаем скидку
UPDATE dbo.Products SET Discount = 10 WHERE ProductId = 1;

После этих операций в dbo.Products — одна строка с текущими данными. В dbo.ProductsHistory — три строки с предыдущими значениями, каждая с точным временем от и до.

Запросы по времени: FOR SYSTEM_TIME

SQL Server поддерживает специальный синтаксис для запросов к данным на определённый момент или за период:

AS OF — состояние на конкретный момент:

-- Какая цена и скидка были вчера в 15:00?
SELECT Name, Price, Discount
FROM dbo.Products
FOR SYSTEM_TIME AS OF '2026-03-22T15:00:00'
WHERE ProductId = 1;

SQL Server сам найдёт строку, у которой ValidFrom <= '2026-03-22T15:00:00' < ValidTo.

FROM... TO — все версии за период:

-- Все изменения за последнюю неделю
SELECT Name, Price, Discount, ValidFrom, ValidTo
FROM dbo.Products
FOR SYSTEM_TIME FROM '2026-03-16' TO '2026-03-23'
WHERE ProductId = 1
ORDER BY ValidFrom;

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

BETWEEN — аналогично FROM...TO, но включает обе границы.

CONTAINED IN — строки, полностью попадающие в интервал (и начало, и конец внутри):

-- Версии, которые были актуальны полностью внутри марта
SELECT *
FROM dbo.Products
FOR SYSTEM_TIME CONTAINED IN ('2026-03-01', '2026-03-31')
WHERE ProductId = 1;

ALL — вообще все версии, включая текущую и всю историю:

SELECT Name, Price, Discount, ValidFrom, ValidTo
FROM dbo.Products
FOR SYSTEM_TIME ALL
WHERE ProductId = 1
ORDER BY ValidFrom;

Превращаем существующую таблицу в temporal

Не обязательно создавать таблицу заново. Можно добавить версионирование к существующей:

-- Шаг 1: добавляем столбцы периода
ALTER TABLE dbo.Clients ADD
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START 
        HIDDEN NOT NULL DEFAULT SYSUTCDATETIME(),
    ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END   
        HIDDEN NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59'),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

-- Шаг 2: включаем версионирование
ALTER TABLE dbo.Clients
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ClientsHistory));

HIDDEN — столбцы не будут возвращаться в SELECT *. Они есть, но не мешают существующему коду. Существующие запросы, ORM, приложения — всё продолжит работать как раньше.

Таблица истории: что внутри

Таблица истории — обычная таблица SQL Server. Те же столбцы, что и основная, но без ограничений (PRIMARY KEY, UNIQUE и другие constraints не наследуются). Можно добавить индексы для ускорения запросов по времени:

-- Кластерный индекс на период — ускоряет FOR SYSTEM_TIME
CREATE CLUSTERED INDEX IX_ProductsHistory_Period
ON dbo.ProductsHistory (ValidTo, ValidFrom)
WITH (DATA_COMPRESSION = PAGE);

DATA_COMPRESSION = PAGE — история обычно большая, компрессия экономит место.

Таблицу истории нельзя напрямую модифицировать (INSERT/UPDATE/DELETE), пока включено SYSTEM_VERSIONING.

Retention policy: автоматическая очистка

История растёт. Для таблицы с миллионами строк и частыми обновлениями таблица истории может стать огромной. Есть поддержка retention policy:

ALTER TABLE dbo.Products
    SET (SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.ProductsHistory,
        HISTORY_RETENTION_PERIOD = 1 YEAR
    ));

SQL Server автоматически удалит строки истории старше года. Для этого нужно включить TEMPORAL_HISTORY_RETENTION на уровне базы:

ALTER DATABASE MyDB SET TEMPORAL_HISTORY_RETENTION ON;

Практический пример: аудит изменений

«Кто поменял скидку клиенту и когда?». Можно узнать так:

SELECT 
    c.ClientName,
    h.Discount AS OldDiscount,
    c.Discount AS CurrentDiscount,
    h.ValidFrom AS ChangedFrom,
    h.ValidTo AS ChangedTo
FROM dbo.Clients c
JOIN dbo.ClientsHistory h 
    ON c.ClientId = h.ClientId
WHERE c.ClientId = 42
    AND h.Discount <> c.Discount
ORDER BY h.ValidFrom DESC;

Или сравнить состояние на две даты:

-- Что изменилось за последние 24 часа
SELECT 
    curr.ClientName,
    prev.Discount AS DiscountYesterday,
    curr.Discount AS DiscountToday
FROM dbo.Clients FOR SYSTEM_TIME AS OF GETUTCDATE() curr
JOIN dbo.Clients FOR SYSTEM_TIME AS OF DATEADD(DAY, -1, GETUTCDATE()) prev
    ON curr.ClientId = prev.ClientId
WHERE curr.Discount <> prev.Discount;

Два FOR SYSTEM_TIME AS OF с разными моментами — и у вас diff между вчера и сегодня.

Тонкости

UTC. ValidFrom/ValidTo хранятся в UTC. SYSUTCDATETIME(), не GETDATE(). Если приложение работает в локальном времени — конвертируйте при отображении.

Нельзя TRUNCATE. Temporal table не поддерживает TRUNCATE TABLE. Только DELETE (который корректно переместит строки в историю).

Изменение схемы. Добавить столбец можно. Удалить немножко сложнее: нужно сначала выключить SYSTEM_VERSIONING, изменить обе таблицы (основную и историю), включить обратно.

ALTER TABLE dbo.Products SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.Products DROP COLUMN SomeOldColumn;
ALTER TABLE dbo.ProductsHistory DROP COLUMN SomeOldColumn;
ALTER TABLE dbo.Products SET (SYSTEM_VERSIONING = ON (
    HISTORY_TABLE = dbo.ProductsHistory
));

Производительность. UPDATE на temporal table чуть дороже — SQL Server делает INSERT в таблицу истории. Для OLTP с тысячами UPDATE/s это ощутимо. Для большинства бизнес‑приложений незаметно. Индекс на (ValidTo, ValidFrom) в таблице истории обязателен для быстрых запросов FOR SYSTEM_TIME.

Temporal Tables закрывают только часть задачи: позволяют восстановить историю изменений. Но когда встаёт вопрос, как не потерять данные, как восстановиться после ошибки и как уверенно работать с SQL Server в боевых условиях, одной этой фичи уже недостаточно. Курс «Разработчик MS SQL Server» здесь уместен как раз потому, что даёт более широкий рабочий контекст: не только отдельные механизмы, но и понимание надёжности, восстановления и эксплуатации базы.

Проверьте, насколько вы готовы к таким сценариям:
^{15-минутное тестирование покажет пробелы и точки роста}➦[Узнать свой уровень]

Приходите на открытый урок 9 апреля в 20:00:
^{Разбор моделей восстановления и реальных кейсов спасения баз} ➦ [Прийти на урок 9 апреля]

Temporal Tables в MS SQL Server: история изменений