SQL Server数据库分区表
一、创建分区表:
1、先创建给分区表使用的分区目录:E:\TMS_DATA

2、创建数据库文件组:
语法:alter database <数据库名> add filegroup <文件组名>
alter database TMS add filegroup TMSGroup1
alter database TMS add filegroup TMSGroup2
alter database TMS add filegroup TMSGroup3
alter database TMS add filegroup TMSGroup4
alter database TMS add filegroup TMSGroup5
alter database TMS add filegroup TMSGroup6
alter database TMS add filegroup TMSGroup7
alter database TMS add filegroup TMSGroup8
alter database TMS add filegroup TMSGroup9
alter database TMS add filegroup TMSGroup10
alter database TMS add filegroup TMSGroup11
alter database TMS add filegroup TMSGroup12
alter database TMS add filegroup TMSGroup13
alter database TMS add filegroup TMSGroup14
alter database TMS add filegroup TMSGroup15
alter database TMS add filegroup TMSGroup16
alter database TMS add filegroup TMSGroup17
alter database TMS add filegroup TMSGroup18
alter database TMS add filegroup TMSGroup19
alter database TMS add filegroup TMSGroup20

3、创建数据文件到文件组里面:
语法:alter database <数据库名称> add file <数据标识> to filegroup <文件组名称>
--<数据标识> (name:文件名,fliename:物理路径文件名,size:文件初始大小kb/mb/gb/tb,filegrowth:文件自动增量kb/mb/gb/tb/%,maxsize:文件可以增加到的最大大小kb/mb/gb/tb/unlimited)
alter database TMS add file
(name=N'TMS1',filename=N'E:\TMS_DATA\TMS1.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup1
alter database TMS add file
(name=N'TMS2',filename=N'E:\TMS_DATA\TMS2.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup2
alter database TMS add file
(name=N'TMS3',filename=N'E:\TMS_DATA\TMS3.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup3
alter database TMS add file
(name=N'TMS4',filename=N'E:\TMS_DATA\TMS4.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup4
alter database TMS add file
(name=N'TMS5',filename=N'E:\TMS_DATA\TMS5.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup5
alter database TMS add file
(name=N'TMS6',filename=N'E:\TMS_DATA\TMS6.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup6
alter database TMS add file
(name=N'TMS7',filename=N'E:\TMS_DATA\TMS7.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup7
alter database TMS add file
(name=N'TMS8',filename=N'E:\TMS_DATA\TMS8.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup8
alter database TMS add file
(name=N'TMS9',filename=N'E:\TMS_DATA\TMS9.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup9
alter database TMS add file
(name=N'TMS10',filename=N'E:\TMS_DATA\TMS10.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup10
alter database TMS add file
(name=N'TMS11',filename=N'E:\TMS_DATA\TMS11.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup11
alter database TMS add file
(name=N'TMS12',filename=N'E:\TMS_DATA\TMS12.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup12
alter database TMS add file
(name=N'TMS13',filename=N'E:\TMS_DATA\TMS13.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup13
alter database TMS add file
(name=N'TMS14',filename=N'E:\TMS_DATA\TMS14.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup14
alter database TMS add file
(name=N'TMS15',filename=N'E:\TMS_DATA\TMS15.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup15
alter database TMS add file
(name=N'TMS16',filename=N'E:\TMS_DATA\TMS16.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup16
alter database TMS add file
(name=N'TMS17',filename=N'E:\TMS_DATA\TMS17.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup17
alter database TMS add file
(name=N'TMS18',filename=N'E:\TMS_DATA\TMS18.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup18
alter database TMS add file
(name=N'TMS19',filename=N'E:\TMS_DATA\TMS19.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup19
alter database TMS add file
(name=N'TMS20',filename=N'E:\TMS_DATA\TMS20.ndf',size=20Mb,filegrowth=20mb,maxsize=unlimited)
to filegroup TMSGroup20
3、执行完成后,右键数据库看文件组跟文件里面是不是多出来啦这些文件组跟文件。

4、使用向导创建分区表:
右键到要分区的表--- >> 存储 --- >> 创建分区 --- >>显示向导视图 --- >> 下一步 --- >> 下一步。。
4.1 按照时间进行分区,找到system_time列作为分区列:

4.2 创建分区函数PF_TIME:

4.3 创建分区方案PS_TIME:

4.4 设置分区边界:




4.5 立即运行,进行分区:

4.6 查看分区情况:


4.7 分区完成后,索引肯定会失效,进行索引重建:


二、分区表维护:
1、创建分区索引:
--创建分区索引语法
create <索引分类> index <索引名称>
on <表名>(列名)
on <分区方案名>(分区依据列名)
--创建分区索引
CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[表名]
(
[列名]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [分区方案名] ([分区依据列名])
举例:
CREATE CLUSTERED INDEX [IDX_EVENTS_SYSTEM_TIME] ON [dbo].[TMS_EVENTS]
(
[SYSTEM_TIME]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON PS_EVENTS_SYSTEM_TIME