Для многих приложений обычным требованием является получение информации о модификациях данных, которые произошли в таблице SQL Server. Таблицы могут иметь несколько обновлений и удалений по разным причинам, и может возникнуть необходимость сохранять различные версии записей. До SQL Server 2016 триггеры обычно использовались для отслеживания изменений данных в таблицах. Однако с появлением SQL Server 2016 была введена встроенная функция, называемая временными таблицами, которая упрощает процесс отслеживания изменений данных.
Что такое временные таблицы?
Временные таблицы – это функция базы данных в SQL Server 2016 и более поздних версиях, которая позволяет автоматически сохранять изменения данных для таблицы. Они полезны для различных бизнес-требований, включая аудит, восстановление данных и управление медленно изменяющимися измерениями.
Аудит
Одним из распространенных случаев использования временных таблиц является аудит. С помощью временных таблиц вы легко можете отслеживать, когда и какие изменения были внесены в конкретную запись. Хотя она не предоставляет информацию о том, кто внес изменения, она все же предлагает ценные сведения о истории изменений данных.
Восстановление данных (DDR)
Потеря данных – обычная проблема в базах данных. С помощью временных таблиц восстановление данных становится намного проще. Даже если записи удалены из базовой таблицы, исторические данные все равно сохраняются в таблице истории, что позволяет восстановить потерянные данные без особых хлопот.
Управление медленно изменяющимися измерениями (SCD)
В хранилищах данных часто требуется поддерживать исторические версии данных. Временные таблицы могут использоваться для управления медленно изменяющимися измерениями, в частности, для типа 2 SCD. Это позволяет избежать необходимости в сложных процессах извлечения для отслеживания изменений в исходных данных.
Устранение неполадок
Временные таблицы также могут быть полезны для устранения неполадок. Анализируя исторические данные, хранящиеся в таблице истории, вы можете получить представление о том, как данные менялись со временем, что поможет вам выявить и устранить проблемы.
Использование временных таблиц в SQL Server
Создание временной таблицы в SQL Server просто. Вот пример:
CREATE TABLE Customers (
CustomerID INT IDENTITY PRIMARY KEY CLUSTERED,
CustomerName VARCHAR(100) NOT NULL,
City VARCHAR(50) NOT NULL,
Status VARCHAR(1) NULL,
SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomersHistory));
В вышеприведенном скрипте ключевые элементы, которые следует отметить:
- Синтаксис
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomersHistory))включает системное версионирование и указывает таблицу истории. - Базовая таблица должна иметь два дополнительных столбца:
SysStartTimeиSysEndTime. Эти столбцы автоматически обновляются для отслеживания времени начала и окончания каждой версии записи.
После создания временной таблицы вы можете запрашивать ее так же, как и любую другую таблицу. Исторические данные автоматически сохраняются в таблице истории, которая имеет ту же структуру, что и базовая таблица.
Извлечение данных из временных таблиц
Извлечение данных из временных таблиц просто. Вы можете использовать фразу FOR SYSTEM_TIME, чтобы указать временной диапазон, для которого вы хотите извлечь данные. Вот несколько примеров:
SELECT *
FROM [Customers]
FOR SYSTEM_TIME BETWEEN '2018-01-01' AND '2019-01-01'
WHERE CustomerID = 2
ORDER BY SysStartTime
SELECT *
FROM [Customers]
FOR SYSTEM_TIME ALL
WHERE CustomerID = 2
ORDER BY SysStartTime
SELECT *
FROM [Customers]
FOR SYSTEM_TIME AS OF '2018-10-20 13:30'
WHERE CustomerID = 2
Первый запрос извлекает все версии записи с CustomerID = 2 в указанном временном диапазоне. Второй запрос извлекает все версии записи без ограничения по датам. Третий запрос извлекает запись так, как она существовала в определенный момент времени.
Ограничения временных таблиц
Хотя временные таблицы предлагают множество преимуществ, есть несколько ограничений, о которых следует помнить:
- Для включения системного версионирования требуется первичный ключ для базовой таблицы.
- Как базовая таблица, так и временная таблица должны находиться в одной и той же базе данных.
- Ограничения, первичные ключи, внешние ключи и ограничения столбцов не поддерживаются для временных таблиц.
- FILETABLE или FILESTREAM не поддерживаются, но поддерживаются типы данных BLOB.
- Вы не можете изменять данные напрямую во временной таблице.
Несмотря на эти ограничения, временные таблицы предоставляют мощный и удобный способ отслеживания изменений данных в SQL Server.
Заключение
Временные таблицы SQL Server – это ценная функция, которая упрощает процесс отслеживания изменений данных в таблицах. Они предлагают такие преимущества, как аудит, восстановление данных и управление медленно изменяющимися измерениями. Понимая, как создавать и запрашивать временные таблицы, вы можете использовать эту функцию для улучшения ваших приложений баз данных.