Azure SQL Server表分区创建及自动新增分区方法咨询

一、创建分区表 #

Azure SQL Server的分区依赖分区函数（定义分区范围规则）和分区方案（将分区映射到文件组），最后基于方案创建分区表。以下是按Order_Year列分区的完整步骤：

1. 创建分区函数 #

分区函数定义分区的边界规则，示例中使用RANGE RIGHT（每个值作为分区的右边界）：

CREATE PARTITION FUNCTION pf_Order_Year (int)
AS RANGE RIGHT FOR VALUES (2015, 2016, 2017, 2018, 2019, 2020);

该函数会生成7个分区：

• 分区1：Order_Year < 2015
• 分区2：2015 ≤ Order_Year < 2016
• ...
• 分区7：Order_Year ≥ 2020

2. 创建分区方案 #

将分区函数映射到文件组（示例中所有分区使用默认PRIMARY文件组，也可指定多个文件组优化IO）：

CREATE PARTITION SCHEME ps_Order_Year
AS PARTITION pf_Order_Year
ALL TO ([PRIMARY]);

3. 创建分区表 #

指定分区列和关联的分区方案，注意分区列必须包含在主键/唯一索引中：

CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    Order_Year INT NOT NULL,
    OrderDate DATETIME,
    CustomerID INT,
    Amount DECIMAL(18,2)
) ON ps_Order_Year(Order_Year);

二、自动添加新分区范围 #

Azure SQL Server无原生自动扩容分区的功能，需通过存储过程+定时作业实现。以新增2021及后续年份分区为例：

1. 创建自动添加分区的存储过程 #

该过程会检查当前分区的最大边界，自动添加下一年的分区：

CREATE PROCEDURE dbo.AddNewOrderYearPartition
AS
BEGIN
    SET NOCOUNT ON;

    -- 获取当前分区函数的最大边界年份
    DECLARE @MaxYear INT;
    SELECT @MaxYear = MAX(value) 
    FROM sys.partition_range_values 
    WHERE function_id = OBJECT_ID('pf_Order_Year');

    -- 计算待添加的下一个年份
    DECLARE @NextYear INT = @MaxYear + 1;

    -- 避免重复添加边界
    IF NOT EXISTS (
        SELECT 1 
        FROM sys.partition_range_values 
        WHERE function_id = OBJECT_ID('pf_Order_Year') 
        AND value = @NextYear
    )
    BEGIN
        -- 拆分分区，添加新的边界
        ALTER PARTITION FUNCTION pf_Order_Year()
        SPLIT RANGE (@NextYear);

        PRINT '已成功添加分区边界: ' + CAST(@NextYear AS VARCHAR(4));
    END
    ELSE
    BEGIN
        PRINT '分区边界 ' + CAST(@NextYear AS VARCHAR(4)) + ' 已存在，无需操作';
    END
END

2. 配置定时作业 #

• 若使用Azure SQL托管实例：启用SQL Agent，创建新作业，添加执行上述存储过程的步骤，设置调度（例如每年12月执行一次，或每月检查）。
• 若使用单一Azure SQL数据库：使用弹性作业代理创建定时任务，定期执行存储过程。

注意事项 #

• 若分区方案使用多文件组，需提前用ALTER PARTITION SCHEME ps_Order_Year NEXT USED [FileGroupName]指定下一个可用文件组，否则拆分分区会失败。
• 拆分分区属于元数据操作，但如果目标分区有数据，会产生短暂锁，建议在业务低峰期执行。

内容的提问来源于stack exchange，提问作者Amarjeet Kushwaha