$PARTITION: примеры (SQL)

Примеры использования

Пример с функцией секционирования по диапазону дат.

CREATE PARTITION FUNCTION pfDateRange (datetime)
AS RANGE RIGHT FOR VALUES ('2024-01-01', '2024-07-01', '2025-01-01');
GO
SELECT $PARTITION.pfDateRange('2024-05-15') AS PartitionNumber;

PartitionNumber
---------------
2

Пример с целочисленным секционированием.

CREATE PARTITION FUNCTION pfIntRange (int)
AS RANGE LEFT FOR VALUES (100, 200, 300);
GO
SELECT 
    $PARTITION.pfIntRange(50) AS Part_50,
    $PARTITION.pfIntRange(150) AS Part_150,
    $PARTITION.pfIntRange(350) AS Part_350;

Part_50  Part_150 Part_350
-------- -------- --------
1        2        4

Похожие функции в MS SQL

Прямых функциональных аналогов $PARTITION в T-SQL нет. Однако для получения информации о секциях применяют другие средства:

• Системные представления: sys.partitions, sys.dm_db_partition_stats. Они предоставляют детальную метаинформацию о всех секциях, но не вычисляют номер для конкретного значения напрямую.
• Функция sys.fn_PhysLocFormatter (или недокументированный %%physloc%%): позволяет получить физическое местоположение строки, которое можно косвенно связать с секцией. Использование менее удобно и прозрачно.

$PARTITION предпочтительнее для сценариев, где требуется именно логическое сопоставление значения с номером секции без обращения к самим таблицам.

Альтернативы в других СУБД и языках

Концепция секционирования и функция, аналогичная $PARTITION, присутствуют не во всех СУБД.

• Oracle: Используется выражение PARTITION FOR (значение) в DML или можно запросить SELECT ... FROM TABLE_NAME PARTITION FOR (значение). Для определения номера секции по значению часто применяют запросы к словарным представлениям, например, USER_TAB_PARTITIONS.
• PostgreSQL: Для определения секции, в которую попадет строка, используют оператор FOR VALUES при создании таблицы-партиции. Прямого аналога функции нет, номер или имя секции определяют путем проверки условий партиционирования.
• MySQL: Для партиционирования по диапазону или списку можно использовать функции PARTITION в запросе EXPLAIN, чтобы увидеть, какие партиции будут затронуты.
• SQLite, Sybase ASE: Не поддерживают встроенное декларативное секционирование, аналогичное MS SQL, поэтому подобная функциональность отсутствует.

Типичные ошибки

1. Использование несуществующей функции секционирования.

SELECT $PARTITION.NonExistentPF(100);

Msg 195, Level 15, State 10, Line 1
'NonExistentPF' is not a recognized built-in function name.

2. Несовпадение типов данных или невозможность неявного преобразования.

CREATE PARTITION FUNCTION pfChar (char(3)) AS RANGE RIGHT FOR VALUES ('100');
GO
SELECT $PARTITION.pfChar(100); -- 100 - целое число

Msg 8114, Level 16, State 1, Line 1
Error converting data type int to char.

3. Попытка использовать функцию в контексте, где она недопустима, например, в CHECK-ограничении.

CREATE TABLE TestTable (id int);
ALTER TABLE TestTable ADD CONSTRAINT chk_part 
CHECK ($PARTITION.pfIntRange(id) > 0);

Msg 1046, Level 15, State 1, Line 2
Subqueries are not allowed in this context. Only scalar expressions are allowed.

Изменения в последних версиях

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

Расширенные примеры

1. Определение распределения данных по секциям в таблице.

SELECT 
    $PARTITION.pfDateRange(OrderDate) AS PartitionNumber,
    COUNT(*) AS RowCount
FROM BigOrders
GROUP BY $PARTITION.pfDateRange(OrderDate)
ORDER BY PartitionNumber;

PartitionNumber RowCount
--------------- ---------
1               15000
2               98000
3               125000
4               5000

2. Массовое удаление данных из конкретной секции.

DELETE FROM BigOrders
WHERE $PARTITION.pfDateRange(OrderDate) = 3;

3. Создание скрипта для обслуживания (очистки) устаревших секций на основе вычисленного номера.

DECLARE @OldPartitionNumber int = $PARTITION.pfDateRange(DATEADD(year, -2, GETDATE()));
IF @OldPartitionNumber > 0
BEGIN
    ALTER TABLE BigOrders TRUNCATE PARTITION @OldPartitionNumber;
END

4. Использование в выражении CASE для маршрутизации логики в зависимости от секции.

SELECT 
    OrderID,
    OrderDate,
    CASE $PARTITION.pfDateRange(OrderDate)
        WHEN 1 THEN 'Архив'
        WHEN 2 THEN 'Активные (первое полугодие)'
        WHEN 3 THEN 'Активные (второе полугодие)'
        ELSE 'Будущий период'
    END AS PartitionCategory
FROM Orders;