Временные таблицы – это мощная функция в SQL Server, которая позволяет легко анализировать данные временных рядов. Они автоматически отслеживают историю на уровне строк, что позволяет легко откатиться от любых ситуаций “ошибка” без необходимости создания резервных копий базы данных. Однако одной из проблем с временными таблицами является количество данных, которые они могут генерировать. Каждое изменение на уровне строк хранится в исторической таблице временной таблицы, что может быстро привести к заполнению дискового пространства и возможным предупреждениям о низком дисковом пространстве.

В SQL Server 2017 CTP3 и более поздних версиях Microsoft представила новую функцию, которая позволяет добавить период хранения во временные таблицы. Это делает очистку старых данных из временной таблицы такой же простой, как указание желаемого периода хранения при создании таблицы:

CREATE TABLE dbo.TableName (
    ...
) WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.TableNameHistory,
        HISTORY_RETENTION_PERIOD = 6 MONTHS
    )
);

Однако, пока все производственные среды не будут обновлены до SQL Server 2017 или более поздних версий, нам нужно вручную автоматизировать процесс с помощью скриптов. Давайте посмотрим, как мы можем очистить старые данные из исторических таблиц в SQL Server 2016.

Сначала нам нужно написать скрипт, который удаляет данные, старше указанного временного интервала, из исторической таблицы. Вот пример:

ALTER TABLE dbo.CarInventory SET (SYSTEM_VERSIONING = OFF);

-- В реальном мире мы бы выполнили некоторые вычисления даты здесь
DECLARE @OneMonthBack DATETIME2 = '2017-06-04';
DELETE FROM dbo.CarInventoryHistory WHERE SysStartTime < @OneMonthBack;

Прежде чем мы сможем удалить данные из исторической таблицы, нам нужно отключить системное версионирование для временной таблицы. SQL Server не позволяет нам удалять данные из исторической таблицы, которая в настоящее время отслеживает временную таблицу. Однако временное отключение системного версионирования может привести к проблемам целостности данных, если другие процессы выполняют операции INSERT, UPDATE или DELETE на временной таблице во время удаления.

Для решения этой проблемы рекомендуется обернуть логику ALTER TABLE и DELETE в транзакцию. Это гарантирует, что любые другие запросы, выполняющиеся против временной таблицы, будут ожидать завершения удаления:

-- Выполните это в окне запроса #1 (удаление данных):
BEGIN TRANSACTION;
ALTER TABLE dbo.CarInventory SET (SYSTEM_VERSIONING = OFF);

-- В реальном мире мы бы выполнили некоторые вычисления даты здесь
DECLARE @OneMonthBack DATETIME2 = '2017-06-04';
DELETE FROM dbo.CarInventoryHistory WITH (TABLOCKX) WHERE SysStartTime < @OneMonthBack;

-- Давайте подождем 10 секунд, чтобы имитировать более длительную операцию удаления
WAITFOR DELAY '00:00:10';

-- Включаем SYSTEM_VERSIONING
ALTER TABLE dbo.CarInventory SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CarInventoryHistory));
COMMIT TRANSACTION;

-- Выполните это в окне запроса #2 во время выполнения вышеуказанного запроса (попытка обновления во время удаления):
UPDATE dbo.CarInventory SET InLot = 0 WHERE CarId = 4;

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

После того, как у вас будет готов скрипт, вы можете запланировать его как задание SQL Agent для выполнения с определенной периодичностью. Это автоматизирует процесс очистки старых данных из исторической таблицы, поддерживая вашу базу данных в чистоте и предотвращая избыточное использование дискового пространства.

Управление временными таблицами и их историческими данными является важным аспектом администрирования SQL Server. Понимая, как очищать старые данные из исторических таблиц, вы можете обеспечить оптимальную производительность и эффективное использование дискового пространства в вашей базе данных.