create partition function test_pf(datetime) as range right for values('2003-10-1','2003-11-1','2003-12-1','2004-1-1','2004-2-1')--創建test表的分區函數
create partition scheme test_ps as partition test_pf to([primary],[primary],[primary],[primary],[primary],[primary])--創建test表的分區方案
create table test(TransactionID int not null,ProductID int not null,ReferenceOrderID int  not null,ReferenceOrderLineID int not null,TransactionDate datetime not null,TransactionType nchar(1) not null,Quantity int not null,ActualCost money not null,ModifiedDate datetime not null) on test_ps(TransactionDate)--創建分區表test
insert test select * from dbo.test00--對test表進行數值插入
create partition function test1_pf(datetime) as range right for values('2003-9-1')--創建表test1的分區函數
create partition scheme test1_ps as partition test1_pf to ([primary],[primary])--創建表test1的分區方案
create table dbo.test1(TransactionID int not null,ProductID int not null,ReferenceOrderID int not null,ReferenceOrderLineID int not null,TransactionDate datetime not null,TransactionType nchar(1) not null,Quantity int not null,ActualCost money not null,ModifiedDate datetime not null) on test1_ps(TransactionDate)--創建分區表test1
insert dbo.test1 select * from dbo.test11--對test1表進行數值插入
alter partition scheme test1_ps next used [primary]
alter partition function test1_pf()
split range('2003-10-1')--對表test1添加新的分區
select $partition.test1_pf(TransactionDate) from dbo.test1 group by $partition.test1_pf(TransactionDate)--查詢表test1中的分區情況
select * from dbo.test1 where $partition.test1_pf(TransactionDate)=2--查詢表test1中分區2中的記錄
alter table dbo.test
add constraint ck_Date check(TransactionDate>'2003-8-31')--對表test建立約束，確保其中TransactionDate全部在2003-8-31之后
alter partition scheme test_ps next used [primary]
alter partition function test_pf()
split range('2003-09-1')--對表test添加新的分區
alter table dbo.test
switch partition 2 to dbo.test1 partition 2--將表test分區2中的數據移動到表test1中分區2
alter partition function test_pf()
merge range('2003-9-1')--對表test合并分區，即第一個分區包含的數據為TransactionDate<2003-10-1
alter partition function test1_pf()
merge range ('2003-9-1')--對表test1合并分區，即第一個分區包含的數據為TransactionDate<2003-10-1
select * from dbo.test1 where $partition.test1_pf(TransactionDate)=1--查詢表test1分區1中的數據
alter partition scheme test_ps next used [primary]
alter partition function test_pf()
split range('2004-3-1')--對表test添加新的分區以接收2004年3月的數據