如何基于日期与部门范围实现数据库表的多列分区？

针对你需要按天和部门范围（每100个部门为一组）分区的需求，结合SQL Server的分区机制，可通过多列复合分区函数实现，具体步骤如下：

1. 创建复合分区函数 #

分区函数需要同时包含日期和部门ID两个列作为分区键，按照日期+部门范围的组合定义边界值。因为你使用了RANGE RIGHT，边界值会作为分区的上限，刚好匹配你要的部门区间（比如101对应1-100的上限）。

示例SQL：

CREATE PARTITION FUNCTION dailyDepPartitionFunction (date, int)
AS RANGE RIGHT FOR VALUES (
    -- 2023-06-01对应的部门分区边界
    ('2023-06-01', 101),
    ('2023-06-01', 201),
    ('2023-06-01', 301),
    -- 2023-06-02对应的部门分区边界
    ('2023-06-02', 101),
    ('2023-06-02', 201),
    ('2023-06-02', 301)
    -- 后续日期可按此格式依次添加
);

2. 创建分区方案 #

分区方案用于将分区函数定义的分区映射到文件组，这里以默认的PRIMARY文件组为例：

CREATE PARTITION SCHEME dailyDepPartitionScheme
AS PARTITION dailyDepPartitionFunction
ALL TO (PRIMARY);

如果需要把不同分区放到不同文件组优化性能，可替换ALL TO (PRIMARY)为指定的文件组列表。

3. 创建分区表 #

创建表时指定使用上述分区方案，并且将日期列和部门ID列作为分区键：

CREATE TABLE BusinessData (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    RecordDate DATE NOT NULL,
    DepartmentID INT NOT NULL,
    -- 其他业务列...
) ON dailyDepPartitionScheme (RecordDate, DepartmentID);

注意：如果表有聚集索引，需确保聚集索引包含分区键列，避免分区对齐问题。

4. 验证分区效果 #

插入测试数据后，可通过以下SQL查看各分区的数据分布：

SELECT 
    $PARTITION.dailyDepPartitionFunction(RecordDate, DepartmentID) AS 分区编号,
    MIN(RecordDate) AS 最小日期,
    MAX(RecordDate) AS 最大日期,
    MIN(DepartmentID) AS 最小部门ID,
    MAX(DepartmentID) AS 最大部门ID,
    COUNT(*) AS 记录数
FROM BusinessData
GROUP BY $PARTITION.dailyDepPartitionFunction(RecordDate, DepartmentID)
ORDER BY 分区编号;

后续维护 #

当需要添加新日期的分区时，只需扩展分区函数的边界值：

-- 添加2023-06-03的三个部门分区
ALTER PARTITION FUNCTION dailyDepPartitionFunction()
SPLIT RANGE (('2023-06-03', 101), ('2023-06-03', 201), ('2023-06-03', 301));

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