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

Решение 1: Растяжение базы данных

Функция растяжения базы данных, введенная в SQL Server 2016 и доступная в стандартной версии с версии SP1, позволяет перемещать исторические и локальные данные в облако. Включив эту функцию на вашей таблице истории времени, вы можете определить функцию на столбце datetime для перемещения данных, старше определенной даты, в вашу базу данных Azure. Хотя это решение обеспечивает автоматизированное управление данными, оно требует наличия базы данных в Azure и учетной записи Azure. Кроме того, запросы исторических данных могут вызывать более высокие затраты, чем вставка и хранение данных в облаке.

Решение 2: Разделение таблицы

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

Решение 3: Пользовательский скрипт

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

Политика сохранения истории времени

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

Пример:

CREATE TABLE TestTemporal(
Id INT CONSTRAINT PK_ID PRIMARY KEY,
CustomerName VARCHAR(50),
StartDate DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, 
EndDate DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartDate, EndDate)
)
WITH (SYSTEM_VERSIONING = ON 
         (HISTORY_TABLE = dbo.TestTemporalHistory, History_retention_period = 2 DAYS )
      ) 

Информацию о сохранении истории можно получить из системной таблицы sys.tables:

SELECT name, temporal_type_desc, history_retention_period, history_retention_period_unit 
FROM sys.tables

Важно отметить, что разделение таблицы доступно только в Enterprise Edition SQL Server. Однако политика сохранения истории предоставляет простое и автоматизированное решение для управления таблицами истории времени во всех версиях SQL Server 2017 и выше.

Вывод

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

Каковы ваши мысли по управлению таблицами истории времени? Поделитесь своими ответами в комментариях ниже!