MSSQL分区表

最近，因日志记录大增，决定试用分区表，记录如下：

一、创建分区表，加载测试数据

use db1
GO
Create PARTITION FUNCTION IntRangePFN(int) /*创建分区函数*/
AS
RANGE LEFT FOR VALUES (
100,    -- X<= 100
200     -- 100<X<=200
)
GO
 /*创建文件组*/
ALTER DATABASE db1 ADD FILEGROUP [100]
GO
ALTER DATABASE db1 ADD FILEGROUP [200]
GO
ALTER DATABASE db1
ADD FILE 
(NAME = N'100',FILENAME = N'F:DataFileGroupdb1100.ndf',SIZE = 512kB,FILEGROWTH = 512kB)
TO FILEGROUP [100]
GO
ALTER DATABASE db1
ADD FILE 
(NAME = N'200',FILENAME = N'F:DataFileGroupdb1200.ndf',SIZE = 512kB,FILEGROWTH = 512kB)
TO FILEGROUP [200]
GO
CREATE PARTITION SCHEME [IntRangePScheme] /*创建建立分区架构*/
AS
PARTITION IntRangePFN TO
( 
  [100],[200],[PRIMARY]
)
GO
/*创建测试表，加载数据*/
USE db1
GO
CREATE TABLE [dbo].[Test](
    [id] int,    
    [name] [nvarchar](100) NULL,
     PRIMARY KEY (id) ON [PRIMARY]
) ON  IntRangePScheme(id)
GO
use db1
go
insert into [test](id,name) values(1,'1')
go
insert into [test](id,name) values(101,'101')
/*验证数据*/
select $partition.IntRangePFN(id),*
from [test] a
order by a.id asc
go

二、分区表的合并，拆分

拆分

--添加一个  200< X <=300 分区
ALTER DATABASE db1 ADD FILEGROUP [300] /*新增加一个NDF文件，用于存储新数据*/
GO
ALTER DATABASE db1
ADD FILE 
(NAME = N'300',FILENAME = N'F:DataFileGroupdb1300.ndf',SIZE = 5MB,FILEGROWTH = 5MB)
TO FILEGROUP [300]
GO
Alter Partition Scheme [IntRangePScheme] Next Used [PRIMARY] /*添加新分区*/
GO 
ALTER PARTITION FUNCTION IntRangePFN()
SPLIT RANGE (300);
go
insert into [test](id,name) values(201,'201')
go
/*验证数据*/
select $partition.IntRangePFN(id),*
from [test] a
order by a.id asc
go

合并

--合并 100< X <=200 , 200< X <=300 两个分区 
Alter Partition Function IntRangePFN() Merge Range(200) 
go
select $partition.IntRangePFN(id),*
from [test] a
order by a.id asc
go

总结：

      1.先创建分区(100,200): X1<=100,100<X2<=200,X3>200 三个区间

      2.将X>200区间 拆分成 200<X1<=300,X2>300两个区间，即(100,200,300);

         拆分之后： 总区间为: X1<=100,100<X2<=200,200<X3<=300,X4>300

     3.合并区间100<X1<=200,200<X2<=300 为100<X<=300

         合并之后： 总区间为：X<=100，100<X1<=300，X2>300

代码下载