Время прочтения: 2 мин.

Предположим, что под хранение определенных данных у Вас есть ограниченное пространство на сервере, например, 100 ГБ, и есть понимание, что со всеми индексами глубина этих данных составит около трех месяцев. Или же под бизнес задачу требуется не более трех месяцев данных. Как автоматизировать данный процесс? Один из возможных вариантов решения, который реализован и работает в одном из проектов опишу ниже.

Шаг 1. Изначальная организация хранения

Представим три месяца данных, условно, как 90 дней. Каждый новый день будем писать в новую партицию. Для этого создадим функцию секционирования с указанием первой даты данных, пример:

CREATE PARTITION FUNCTION [FN_PRT_EXAMPLE] (date) AS RANGE RIGHT FOR VALUES (N'2020-05-01T00:00:00.000')

Далее на основе созданной функции создаем схему секционирования:

CREATE PARTITION SCHEME [SC_PRT_EXAMPLE] AS PARTITION [FN_PRT_EXAMPLE] TO ([EXAMPLE_FLGRP], [EXAMPLE_FLGRP])

Шаг 2. Загрузка данных

Перед тем как приступить к загрузке новой даты (‘2020-05-02’), вне зависимости от выбранного Вами способа обновления (прямой загрузки в таблицу или загрузке во временную таблицу с последующим подключением ее как партиции), необходимо проверить наличие загружаемой даты в функции секционирования. В случае, если дата отсутствует, добавляем ее в функцию и описываем для данной партиции месторасположение. Пример:

USE EXMPL_DB
GO
if 
(select pv.value from 
	sys.partition_functions pf,
	sys.partition_range_values pv
where pf.name = 'FN_PRT_EXAMPLE'
and pf.function_id = pv.function_id
and cast(pv.value as date) = '2020-05-02') is null
begin
ALTER PARTITION SCHEME [SC_PRT_EXAMPLE] NEXT USED[EXAMPLE_FLGRP];
ALTER PARTITION FUNCTION [FN_PRT_EXAMPLE] () SPLIT RANGE ('2020-05-02');
End

После добавления секции можно приступать к загрузке.

Шаг 3. Удаление устаревших данных

После того, как произведена плановая загрузка данных проверяем, какие партиции нужно пометить к удалению с учётом планируемой глубины (в нашем случае 90 дней):

USE EXMPL_DB
GO
select  cast(partition_number as nvarchar(10)) partition_number from 
(select ROW_NUMBER() OVER (order by partition_number desc) rownum, partition_number
from sys.partitions where object_id =  object_id('EXMPL_DB.DBO.TABLENAME')
and rows > 0
group by partition_number) count_partitions
where rownum > 90 order by partition_number

Список полученных партиций можно передать объектом в SSIS либо сгенерировать динамический запрос вида:

TRUNCATE TABLE DBO.TABLENAME WITH (PARTITIONS(X))

где X – номер партиции.

Очищенные секции можно удалить, используя команду, указывая дату для удаления:

ALTER PARTITION FUNCTION [FN_PRT_EXAMPLE] () MERGE RANGE ('2020-05-01');

Плюс данной операции перед «delete» в том, что truncate/drop партиции занимает примерно 1 секунду. При этом вычищается табличное пространство. Секционированные таблицы проще обслуживать, например, перенести устаревшие данные в архивную таблицу. Применение данных средств оправдано в случае работы с объемными таблицами. Таблицы небольшого размера для сжатия проще периодически полностью переписывать заново.