Работа с версионностью данных: Temporal Tables

Что такое Temporal Tables и зачем они нужны? ⏳

Temporal Tables (временные таблицы) — это мощный инструмент в SQL, который автоматически сохраняет историю изменений данных. Представьте, что у вас есть машина времени для ваших таблиц! Вы можете узнать:

Какие данные были в прошлом
Когда именно они изменились
Кто (или что) эти изменения внес

Реальные кейсы использования:

🔍 Аудит изменений данных
🔄 Восстановление информации после ошибочного удаления
📈 Анализ тенденций и изменений во времени

Как устроены Temporal Tables? 🧐

Временные таблицы состоят из двух частей:

Основная таблица — хранит текущие данные
Историческая таблица — автоматическое хранилище всех предыдущих версий

-- Пример структуры временной таблицы
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    Price 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.ProductsHistory));

Создаём свою первую Temporal Table 🛠️

Давайте создадим таблицу сотрудников с отслеживанием истории изменений:

-- Включаем системное управление версионностью
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Position NVARCHAR(100) NOT NULL,
    Salary DECIMAL(10, 2) NOT NULL,
    Department NVARCHAR(50) NOT NULL,
    -- Системные столбцы для версионности
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory));

🔹 GENERATED ALWAYS AS ROW START/END — автоматическое заполнение временных меток
🔹 HIDDEN — необязательно, но удобно для обычных запросов
🔹 PERIOD FOR SYSTEM_TIME — определяем временной период действия записи

Работаем с историей данных 🔍

Теперь мы можем запросить данные на конкретный момент времени:

-- Все сотрудники, которые были в базе на 1 января 2023 года
SELECT * FROM Employees
FOR SYSTEM_TIME AS OF '2023-01-01';

-- Изменения в определённый период (от и до)
SELECT * FROM Employees
FOR SYSTEM_TIME BETWEEN '2023-01-01' AND '2023-06-30';

-- Все когда-либо существовавшие версии для конкретного сотрудника
SELECT * FROM Employees
FOR SYSTEM_TIME ALL
WHERE EmployeeID = 42;

Практический пример: отслеживаем изменения зарплат 💰

Давайте смоделируем ситуацию с изменениями зарплат:

-- Добавляем сотрудника
INSERT INTO Employees (EmployeeID, Name, Position, Salary, Department)
VALUES (1, 'Иван Петров', 'Разработчик', 100000, 'IT');

-- Через месяц повышаем зарплату (2023-02-15)
UPDATE Employees 
SET Salary = 120000 
WHERE EmployeeID = 1;

-- Ещё через полгода (2023-08-20) меняем должность и зарплату
UPDATE Employees 
SET Position = 'Старший разработчик', Salary = 150000
WHERE EmployeeID = 1;

Теперь посмотрим историю:

-- Все изменения по сотруднику
SELECT e.EmployeeID, e.Name, e.Position, e.Salary, 
       e.SysStartTime AS 'Действительно с', 
       e.SysEndTime AS 'Действительно до'
FROM Employees FOR SYSTEM_TIME ALL AS e
WHERE e.EmployeeID = 1
ORDER BY e.SysStartTime;

Важные особенности Temporal Tables ⚠️

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

Продвинутые техники работы 🚀

Восстановление данных из истории

-- Восстановим запись, удалённую неделю назад
INSERT INTO Employees (EmployeeID, Name, Position, Salary, Department)
SELECT EmployeeID, Name, Position, Salary, Department
FROM Employees FOR SYSTEM_TIME AS OF DATEADD(day, -7, GETDATE())
WHERE EmployeeID = 42;

Анализ изменений во времени

-- Средняя зарплата по отделам на начало каждого месяца
SELECT 
    DATEFROMPARTS(YEAR(ValidFrom), MONTH(ValidFrom), 1) AS MonthStart,
    Department,
    AVG(Salary) AS AvgSalary
FROM Employees FOR SYSTEM_TIME ALL
GROUP BY DATEFROMPARTS(YEAR(ValidFrom), MONTH(ValidFrom), 1), Department
ORDER BY MonthStart, Department;

Когда Temporal Tables — не лучший выбор ❌

Высокочастотные изменения — для данных, которые меняются каждую секунду
Очень большие таблицы — исторические данные могут занять много места
Уже есть кастомное решение — если реализован собственный механизм аудита

Подводим итоги 🎯

Temporal Tables — это:

✅ Встроенный механизм версионности без написания дополнительного кода
✅ Полная история изменений "из коробки"
✅ Простые запросы к данным на любой момент времени
✅ Надёжное решение для аудита и восстановления данных

-- Отключаем версионность (если нужно)
ALTER TABLE Employees SET (SYSTEM_VERSIONING = OFF);
-- Включаем обратно
ALTER TABLE Employees SET (SYSTEM_VERSIONING = ON);