--***********分区表实验******************* --* Write by yuch_sheng on 2020-07-05 * --************************************** --新建文件组 USE master GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILEGROUP [FILEGROUPOLD] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILEGROUP [FILEGROUP202001] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILEGROUP [FILEGROUP202002] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILEGROUP [FILEGROUP202003] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILEGROUP [FILEGROUP202004] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILEGROUP [FILEGROUP202005] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILEGROUP [FILEGROUP202006] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILEGROUP [FILEGROUP202007] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILEGROUP [FILEGROUP202008] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILEGROUP [FILEGROUP202009] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILEGROUP [FILEGROUP202010] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILEGROUP [FILEGROUP202011] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILEGROUP [FILEGROUP202012] GO --查看文件组信息 sp_helpfilegroup -- 向文件组中添加文件 ALTER DATABASE [MES_WORKBECH_TD] ADD FILE (NAME = N'FILEOLD', FILENAME = N'C:dataSQLDB_DATAMES_WORKBECH_OLD.mdf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FILEGROUPOLD] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILE ( NAME = N'FILE202001', FILENAME = N'C:dataSQLDB_DATAMES_WORKBECH_202001.mdf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FILEGROUP202001] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILE ( NAME = N'FILE202002', FILENAME = N'C:dataSQLDB_DATAMES_WORKBECH_202002.mdf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FILEGROUP202002] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILE ( NAME = N'FILE202003', FILENAME = N'C:dataSQLDB_DATAMES_WORKBECH_202003.mdf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FILEGROUP202003] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILE ( NAME = N'FILE202004', FILENAME = N'C:dataSQLDB_DATAMES_WORKBECH_202004.mdf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FILEGROUP202004] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILE ( NAME = N'FILE202005', FILENAME = N'C:dataSQLDB_DATAMES_WORKBECH_202005.mdf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FILEGROUP202005] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILE ( NAME = N'FILE202006', FILENAME = N'C:dataSQLDB_DATAMES_WORKBECH_202006.mdf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FILEGROUP202006] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILE ( NAME = N'FILE202007', FILENAME = N'C:dataSQLDB_DATAMES_WORKBECH_202007.mdf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FILEGROUP202007] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILE ( NAME = N'FILE202008', FILENAME = N'C:dataSQLDB_DATAMES_WORKBECH_202008.mdf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FILEGROUP202008] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILE ( NAME = N'FILE202009', FILENAME = N'C:dataSQLDB_DATAMES_WORKBECH_202009.mdf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FILEGROUP202009] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILE ( NAME = N'FILE202010', FILENAME = N'C:dataSQLDB_DATAMES_WORKBECH_202010.mdf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FILEGROUP202010] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILE ( NAME = N'FILE202011', FILENAME = N'C:dataSQLDB_DATAMES_WORKBECH_202011.mdf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FILEGROUP202011] GO ALTER DATABASE [MES_WORKBECH_TD] ADD FILE ( NAME = N'FILE202012', FILENAME = N'C:dataSQLDB_DATAMES_WORKBECH_202012.mdf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FILEGROUP202012] GO --创建分区函数 CREATE PARTITION FUNCTION [PFUN_DATETIME](datetime) AS RANGE RIGHT FOR VALUES ( N'2020-01-01T00:00:00', N'2020-02-01T00:00:00', N'2020-03-01T00:00:00', N'2020-04-01T00:00:00', N'2020-05-01T00:00:00', N'2020-06-01T00:00:00', N'2020-07-01T00:00:00', N'2020-08-01T00:00:00', N'2020-09-01T00:00:00', N'2020-10-01T00:00:00', N'2020-11-01T00:00:00', N'2020-12-01T00:00:00' ); GO --创建分区方案 CREATE PARTITION SCHEME [PS_PFUN_DATETIME] AS PARTITION [PFUN_DATETIME] TO ( [FILEGROUPOLD], [FILEGROUP202001], [FILEGROUP202002], [FILEGROUP202003], [FILEGROUP202004], [FILEGROUP202005], [FILEGROUP202006], [FILEGROUP202007], [FILEGROUP202008], [FILEGROUP202009], [FILEGROUP202010], [FILEGROUP202011], [FILEGROUP202012], [PRIMARY] ); -- 针对分区列创建聚集索引, -- 此时表中的数据会按分区方案的定义重排在不同的文件组中 USE [MES_WORKBECH_TD] GO CREATE CLUSTERED INDEX [cidx_createDT] ON [dbo].[PRODUCT_WORKORDER_PROCESS_PU] ( [CreateDT] ) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PS_PFUN_DATETIME]([CreateDT]) --查看每给分区的记录数 select $PARTITION.PFUN_DATETIME(CreateDT) as 分区编号, count(ID) as 记录数 from [PRODUCT_WORKORDER_PROCESS_PU] group by $PARTITION.PFUN_DATETIME(CreateDT) --分区结合index提升系统性能 select count(1) from [PRODUCT_WORKORDER_PROCESS_PU] a where a.CreateDT > '2020-05-25 11:53:32.093' --产线计划的建单时间 and a.LinePlanId = '2E03C2E1-3809-47EC-9014-21B30B9C532D' and a.ProcessCode = 'PU_P003' and a.State = '1' and a.Result = '1' select * from [PRODUCT_WORKORDER_PROCESS_PU] a where a.CreateDT > '2020-05-25 11:53:32.093' and a.ProcessCode = 'PU_P003' and a.TrackInBarCode = '5473347I919C520221SA1014245' --and a.TrackOutBarCode = '5473347I919C520221SA1014245' drop index [idx_product_workorder_process_pu_1] on [PRODUCT_WORKORDER_PROCESS_PU]; create index [idx_product_workorder_process_pu_1] on [PRODUCT_WORKORDER_PROCESS_PU] ( LinePlanId,ProcessCode,State,Result ) create index [idx_product_workorder_process_pu_2] on [PRODUCT_WORKORDER_PROCESS_PU] ( TrackInBarCode,ProcessCode ) include(id); create index [idx_product_workorder_process_pu_3] on [PRODUCT_WORKORDER_PROCESS_PU] ( TrackOutBarCode,ProcessCode ) include(id) --***************************************** --* 分区的合并与新增 * --***************************************** --新建第一季度2020Q1文件组 ALTER DATABASE [MES_WORKBECH_TD] ADD FILEGROUP [FILEGROUP2020Q1] GO --向2020Q1文件组中添加文件 ALTER DATABASE [MES_WORKBECH_TD] ADD FILE ( NAME = N'FILE2020Q1', FILENAME = N'C:dataSQLDB_DATAMES_WORKBECH_2020Q1.mdf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FILEGROUP2020Q1] GO --合并分区 ALTER PARTITION FUNCTION PFUN_DATETIME() MERGE RANGE (N'2020-01-01T00:00:00'); ALTER PARTITION FUNCTION PFUN_DATETIME() MERGE RANGE (N'2020-02-01T00:00:00'); ALTER PARTITION FUNCTION PFUN_DATETIME() MERGE RANGE (N'2020-03-01T00:00:00'); --观察各分区记录数的变化,可以发现分区数减少了，原分区2,3,4已被合并 select $PARTITION.PFUN_DATETIME(CreateDT) as 分区编号, count(ID) as 记录数 from [PRODUCT_WORKORDER_PROCESS_PU] group by $PARTITION.PFUN_DATETIME(CreateDT) --分区新增,将2020年第一季度的数据放入FILEGROUP2020Q1文件组 --选择文件组 ALTER PARTITION SCHEME PS_PFUN_DATETIME NEXT USED [FILEGROUP2020Q1] ; --修改分区函数 ALTER PARTITION FUNCTION PFUN_DATETIME() SPLIT RANGE (N'2020-01-01T00:00:00.000') ; --观察各分区记录数的变化,可以发现多了一个分区 select $PARTITION.PFUN_DATETIME(CreateDT) as 分区编号, count(ID) as 记录数 from [PRODUCT_WORKORDER_PROCESS_PU] group by $PARTITION.PFUN_DATETIME(CreateDT) --如果分区变动比较大不推荐用合并和删除的方法，因为容易出错， --如果分12个月建议像下面一样，先将分区表转换为普通表，再把普通表分区。 -- a)删除分区索引,删除分区索引后，并没有变成普通表 drop index [cidx_createDT] on [dbo].[PRODUCT_WORKORDER_PROCESS_PU]; -- b)在原有分区索引字段，建立普通索引 CREATE CLUSTERED INDEX [cidx_createDT] ON [dbo].[PRODUCT_WORKORDER_PROCESS_PU] ( [CreateDT] ) ON[PRIMARY]; -- c) 删除普通萦引 drop index [cidx_createDT] on [dbo].[PRODUCT_WORKORDER_PROCESS_PU]; -- d) 再将普通表做分区 CREATE CLUSTERED INDEX [cidx_createDT] ON [dbo].[PRODUCT_WORKORDER_PROCESS_PU] ( [CreateDT] ) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PS_PFUN_DATETIME]([CreateDT])