SQL2005分区表创建与修改

use test

--1、增加数据库的文件组
alter database test add filegroup fg1
alter database test add filegroup fg2
alter database test add filegroup fg3

--2、为上面的文件组设定数据存储文件
alter database test add file(name=N'f1', filename=N'D:\\dbs\db_test\f1.ndf') to filegroup fg1  --注意，路径文件夹必须已存在
alter database test add file(name=N'f2', filename=N'D:\\dbs\db_test\f2.ndf') to filegroup fg2
alter database test add file(name=N'f3', filename=N'D:\\dbs\db_test\f3.ndf') to filegroup fg3

 

--3、创建分区函数（分区函数定义了分区类型，区域数及区域边界）
create partition function ptfn_gprs(datetime)
as
 range right for values(
   '2011-10-01'
  ,'2011-11-01'
 )

--4、创建分区方案（分区方案设定了各个分区将数据存储到哪里文件组）
create partition scheme ptsch_gprs
as
 partition ptfn_gprs to(
   fg1
  ,fg2
  ,fg3
 )

--5、创建分区表（分区表再一开始就必须指定分区方案及参考字段）
create table gprs_t_in
(
  gprsId int identity(1,1)
 ,inTime datetime
 ,constraint pk_gprs_t_in primary key(gprsId, inTime)  --注意此处，分区字段必须是索引
)
on ptsch_gprs(inTime)

 

--6、插入数据
insert into dbo.gprs_t_in(inTime) values('2011-08-15')
insert into dbo.gprs_t_in(inTime) values('2011-09-15')
insert into dbo.gprs_t_in(inTime) values('2011-10-15')
insert into dbo.gprs_t_in(inTime) values('2011-11-15')
insert into dbo.gprs_t_in(inTime) values('2011-12-15')

--7、查看数据存放在哪个分区，哪个文件组
select sp.destination_id 分区号, fg.name 文件组, x.*
from sys.destination_data_spaces sp
  join sys.partition_schemes scm on sp.partition_scheme_id = scm.data_space_id
  join sys.filegroups fg on fg.data_space_id = sp.data_space_id
  join (SELECT $PARTITION.ptfn_gprs(inTime) 分区号, * FROM dbo.gprs_t_in) x on x.分区号=sp.destination_id
order by 1

--8、查看数据在各个分区的数据量
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('gprs_t_in') order by 4

 

--9、修改（增加）分区

 --9.1 重复步骤 1、2，增加gprs4（此步骤可选）
 alter database test add filegroup fg4
 alter database test add file(name=N'f4', filename=N'D:\\dbs\db_test\f4.ndf') to filegroup fg4

 --9.2 修改分区方案,next used指定了新增的分区使用的文件组
 alter partition scheme ptsch_gprs next used fg4

 --9.3 修改分区函数（新增的分区为被拆分的分区的右边区域）
 alter partition function ptfn_gprs() split range('2011-12-01')

 --9.4 查看数据存放在哪个分区
 select sp.destination_id 分区号, fg.name 文件组, x.*
 from sys.destination_data_spaces sp
   join sys.partition_schemes scm on sp.partition_scheme_id = scm.data_space_id
   join sys.filegroups fg on fg.data_space_id = sp.data_space_id
   join (SELECT $PARTITION.ptfn_gprs(inTime) 分区号, * FROM dbo.gprs_t_in) x on x.分区号=sp.destination_id
 order by 1

 --9.5、查看数据在各个分区的数据量
 SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('gprs_t_in') order by 4

 

--10、删除（合并）分区

 --10.1 将分割点合并掉
 alter partition function ptfn_gprs() merge range('2011-11-01')

 --10.2 查看数据存放在哪个分区（合并后的分区相当于保留并扩展了左边的分区）
 select sp.destination_id 分区号, fg.name 文件组, x.*
 from sys.destination_data_spaces sp
   join sys.partition_schemes scm on sp.partition_scheme_id = scm.data_space_id
   join sys.filegroups fg on fg.data_space_id = sp.data_space_id
   join (SELECT $PARTITION.ptfn_gprs(inTime) 分区号, * FROM dbo.gprs_t_in) x on x.分区号=sp.destination_id
 order by 1

 

--11、查看元数据，我们可以通过以下三个视图来观察我们创建的分区函数，分区方案，边界点值等。
select * from sys.partition_functions  
select * from sys.partition_range_values
select * from sys.partition_schemes

 

--12、分区表的索引建立同普通表一样，没有大区别，但是内部机制不一样，暂不细究