Sql Server分区表操作

--1.创建数据库文件组

--2.创建数据文件，并为数据文件分配文件组

--3.创建分区函数
CREATE PARTITION FUNCTION Function_DateTime ( DATETIME )
AS RANGE 
FOR VALUES('2019-01-01', '2020-01-01', '2021-01-01', '2022-01-01','2023-01-01','2024-01-01')

--4.创建分区方案
CREATE PARTITION SCHEME Scheme_DateTime
AS PARTITION Function_DateTime
TO ( [primary],SECTION2019, SECTION2020, SECTION2021, SECTION2022, SECTION2023,SECTION2024 )

--5.将普通表转换为分区表
--5.1主键字段上默认创建聚集索引，删除主键的聚集索引
ALTER TABLE DingDan_FaHuo DROP CONSTRAINT PK_DingDan_FaHuo
--5.2重新创建主键非聚集索引
ALTER TABLE DingDan_FaHuo ADD CONSTRAINT PK_DingDan_FaHuo PRIMARY KEY NONCLUSTERED (ID ASC)
--5.3创建使用分区方案的聚集索引
CREATE CLUSTERED INDEX idx_CreateDate ON DingDan_FaHuo ( CreateDate )
ON Scheme_DateTime ( CreateDate )

--6.查看调整后DingDan_FaHuo表记录的物理保存情况：
select CreateDate from DingDan_FaHuo where $partition.Function_DateTime(CreateDate) = 1
select CreateDate from DingDan_FaHuo where $partition.Function_DateTime(CreateDate) = 2
select CreateDate from DingDan_FaHuo where $partition.Function_DateTime(CreateDate) = 3
select CreateDate from DingDan_FaHuo where $partition.Function_DateTime(CreateDate) = 4
select CreateDate from DingDan_FaHuo where $partition.Function_DateTime(CreateDate) = 5
select CreateDate from DingDan_FaHuo where $partition.Function_DateTime(CreateDate) = 6

　　参考两篇博文：

https://blog.csdn.net/weixin_33887443/article/details/85670338?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-7.control&dist_request_id=&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-7.control

https://blog.csdn.net/DBA_Huangzj/article/details/8757945?spm=1001.2014.3001.5501