sqlserver 2008 表分区测试

==============================================================================================

sqlserver 2008 表分区

==============================================================================================

1、创建数据库、数据文件组及数据文件
2、创建分区函数，确定分区依据
3、创建分区方案，关联分区函数
4、创建分区表，指定分区方案
5、查询分区（$partition.分区函数()）
6、增加分区,同时修改分区方案(next used)，修改分区函数(split range)
7、删除分区（合并分区(merge range)）

实例：
--创建数据库及相关数据文件组和数据文件
--drop database test;
create database test 
on primary (name=N'test',filename=N'D:\partition\table\test\test.mdf',size=10,filegrowth=1),
filegroup FG01 (name=N'test01',filename=N'D:\partition\table\test\test01.ndf',size=10,filegrowth=1),
filegroup FG02 (name=N'test02',filename=N'D:\partition\table\test\test02.ndf',size=10,filegrowth=1),
filegroup FG03 (name=N'test03',filename=N'D:\partition\table\test\test03.ndf',size=10,filegrowth=1),
filegroup FG04 (name=N'test04',filename=N'D:\partition\table\test\test04.ndf',size=10,filegrowth=1),
filegroup FG05 (name=N'test05',filename=N'D:\partition\table\test\test05.ndf',size=10,filegrowth=1)
log on (name=N'log',filename=N'D:\partition\table\test\log.ldf',size=10,filegrowth=1)
go

--创建分区函数
--drop partition function pf_test;
use test;
create partition function pf_test(date) as range right for values('2013-11-01','2013-11-03','2013-11-05','2013-11-07');
go

--创建分区方案
--drop partition scheme ps_test;
use test;
create partition scheme ps_test as partition pf_test to(FG01,FG02,FG03,FG04,FG05);
go

--创建分区表
--drop table test;
use test;
create table test(
aa date,
bb int null,
cc char(2) null,
constraint pk_test primary key(aa)
) on ps_test(aa);
go

--添加新分区
use test;
alter database test add filegroup FG06;
go
alter database test add file (name=N'test06',filename=N'D:\partition\table\test\test06.ndf',size=10,filegrowth=1) to filegroup FG06;
go
--修改分区方案
use test;
alter partition scheme ps_test next used FG06;
--修改分区函数
use test;
alter partition function pf_test() split range('2013-11-09')
go

--删除分区(合并分区)
use test;
alter partition function pf_test() merge range('2013-11-09')
go

--删除数据文件组及数据文件
use test;
alter database test remove file test06;
alter database test remove filegroup FG06

--查看元数据
select * from sys.partition_functions 

select * from sys.partition_range_values 

select * from sys.partition_schemes

--插入测试数据
use test;
insert into test
values
('2013-11-01',1,'a');
go
insert into test
values
('2013-11-02',2,'a');
go

insert into test
values
('2013-11-03',3,'a');
go
insert into test
values
('2013-11-04',4,'a');
go

insert into test
values
('2013-11-05',5,'a');
go
insert into test
values
('2013-11-06',6,'a');
go

insert into test
values
('2013-11-07',7,'a');
go
insert into test
values
('2013-11-08',8,'a');
go

insert into test
values
('2013-11-09',9,'a');
go
insert into test
values
('2013-11-10',10,'a');
go

--分区数据查询
select * from test
select * from test where test.$partition.pf_test(aa)=1
select * from test where test.$partition.pf_test(aa)=2
select * from test where test.$partition.pf_test(aa)=3
select * from test where test.$partition.pf_test(aa)=4
select * from test where test.$partition.pf_test(aa)=5
select * from test where test.$partition.pf_test(aa)=6 --不存在为空，但不会报错

==============================================================================================
sqlserver 2008 表分区
==============================================================================================