SQL Server表分区

--1.创建文件分组
ALTER DATABASE [TESTDB] ADD FILEGROUP FileGroupDate0;
ALTER DATABASE [TESTDB] ADD FILEGROUP FileGroupDate1;
ALTER DATABASE [TESTDB] ADD FILEGROUP FileGroupDate2;
ALTER DATABASE [TESTDB] ADD FILEGROUP FileGroupDate3;
ALTER DATABASE [TESTDB] ADD FILEGROUP FileGroupDate4;

--2.为每个文件组增加一个数据文件
ALTER DATABASE [TESTDB]
ADD FILE(
NAME=N'FILEDate0',
FILENAME=N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\OrderDate\FILEDate0.ndf',
SIZE=5MB, MAXSIZE=UNLIMITED
,FILEGROWTH=5MB
)
TO FILEGROUP FileGroupDate0 --文件组
GO

ALTER DATABASE [TESTDB]
ADD FILE(
NAME=N'FILEDate1',
FILENAME=N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\OrderDate\FILEDate1.ndf',
SIZE=5MB, MAXSIZE=UNLIMITED
,FILEGROWTH=5MB
)
TO FILEGROUP FileGroupDate1 --文件组
GO

ALTER DATABASE [TESTDB]
ADD FILE(
NAME=N'FILEDate2',
FILENAME=N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\OrderDate\FILEDate2.ndf',
SIZE=5MB, MAXSIZE=UNLIMITED
,FILEGROWTH=5MB
)
TO FILEGROUP FileGroupDate2 --文件组
GO

ALTER DATABASE [TESTDB]
ADD FILE(
NAME=N'FILEDate3',
FILENAME=N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\OrderDate\FILEDate3.ndf',
SIZE=5MB, MAXSIZE=UNLIMITED
,FILEGROWTH=5MB
)
TO FILEGROUP FileGroupDate3 --文件组
GO

ALTER DATABASE [TESTDB]
ADD FILE(
NAME=N'FILEDate4',
FILENAME=N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\OrderDate\FILEDate4.ndf',
SIZE=5MB, MAXSIZE=UNLIMITED
,FILEGROWTH=5MB
)
TO FILEGROUP FileGroupDate4 --文件组
GO

--3.创建分区函数
CREATE PARTITION FUNCTION OrderDateFunction(date) --函数名
AS RANGE RIGHT --LEFT与RIGHT决定了边界属于左边还是右边
FOR VALUES
('2017-01-01','2018-01-01','2019-01-01','2020-01-01') --数据将分为5部分：date<2016-12-31,2017-01-01->2017-12-31,2018-01-01->2018-12-31,2019-01-01->2019-12-31
GO

--4.创建分区方案,定义分区架构
CREATE PARTITION SCHEME [OrderDateScheme]
AS PARTITION OrderDateFunction --依据的分区函数
TO (FileGroupDate0,FileGroupDate1,FileGroupDate2,FileGroupDate3,FileGroupDate4) --数量要与分区函数中的数量（分区函数中将数据分成了5段,对应上面创建的文件分组）一致

--5.在现有表的基础上依据分区创建索引
CREATE CLUSTERED INDEX ix_clu_on_OrderDateFunction_for_DM_PurchaseOrderTP ON TableA_PurchaseOrder([OrderDate]) --普通创建到此就可
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [OrderDateScheme]([OrderDate]); --依据表分区创建索引

CREATE CLUSTERED INDEX ix_clu_on_OrderDateFunction_for_DM_PurchaseOrderTR ON TableB_PurchaseOrder([OrderDate]) --普通创建到此就可
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [OrderDateScheme]([OrderDate]); --依据表分区创建索引

 

参考：