基于创建时间实现SQL Server表分区（无需额外存储分区键）

当然可以做到！你完全不需要在表中新增物理列来存储那个yyyyMM格式的分区键，SQL Server支持用确定性计算列作为分区键，而且这个计算列是虚拟的，不会占用额外的存储空间。下面我一步步给你讲怎么实现：

1. 先创建分区函数 #

分区函数用来定义分区的边界值，我们按yyyyMM的整数格式来划分，比如你需要2019年12月的分区，那边界值就包含201912这类整数。这里用RANGE RIGHT（你也可以根据需求用RANGE LEFT，注意边界的包含关系）：

CREATE PARTITION FUNCTION PF_Created_Month (int)
AS RANGE RIGHT FOR VALUES 
(201901, 201902, 201911, 201912, 202001); -- 把你需要覆盖的月份边界都列出来

解释一下：RANGE RIGHT意味着每个边界值属于右边的分区，比如201912这个边界，会把>=201912且<202001的数据分到对应分区，正好对应2019年12月的所有数据。

2. 创建分区方案 #

分区方案用来把分区函数的各个分区映射到文件组上，如果你不需要单独的文件组，直接用默认的PRIMARY即可：

CREATE PARTITION SCHEME PS_Created_Month
AS PARTITION PF_Created_Month
ALL TO ([PRIMARY]); -- 所有分区都放在PRIMARY文件组，也可以指定不同文件组做隔离

3. 创建带虚拟计算列的分区表 #

这是核心步骤：我们创建一个虚拟计算列，表达式就是YEAR(created_time)*100 + MONTH(created_time)，用这个计算列作为分区键，而且这个列不会被物理存储：

CREATE TABLE Your_Table_Name
(
    Id INT IDENTITY(1,1),
    created_time DATETIME NOT NULL,
    -- 其他业务列...
    Month_Partition_Key AS YEAR(created_time)*100 + MONTH(created_time), -- 虚拟计算列，不占存储
    PRIMARY KEY (Id, Month_Partition_Key) -- 分区表的主键必须包含分区键
) ON PS_Created_Month (Month_Partition_Key);

这里要注意：分区表的主键（或唯一索引）必须包含分区键，所以我们把计算列加入了主键组合，这样主键也会跟着分区，避免后续索引性能问题。

4. 插入数据的自动分区效果 #

当你插入数据时，比如执行：

INSERT INTO Your_Table_Name (created_time)
VALUES ('2019-12-01 10:30:00');

SQL Server会自动计算YEAR('2019-12-01')*100 + MONTH('2019-12-01') = 201912，然后根据分区函数把这条数据放到对应的201912分区里，全程不需要你手动指定分区，完全自动完成。

后续维护：新增分区 #

如果后续需要扩展到新的月份，比如2020年2月，只需要执行以下语句来新增分区边界：

-- 指定下一个分区使用的文件组（这里还是用PRIMARY）
ALTER PARTITION SCHEME PS_Created_Month NEXT USED [PRIMARY];
-- 拆分出202002的分区
ALTER PARTITION FUNCTION PF_Created_Month() SPLIT RANGE (202002);

关键注意点 #

• 计算列必须是确定性的：YEAR()和MONTH()都是确定性函数，所以这个计算列完全符合分区键的要求，不会有问题。
• 不需要持久化计算列：如果你的查询经常用到这个yyyyMM的值，可以把计算列标记为PERSISTED（在计算列后加PERSISTED），这样SQL Server会把计算值存储起来，提升查询性能；如果只是用来分区，不用持久化也完全没问题。

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