参考自：http://msdn.microsoft.com/zh-cn/library/ms345146(SQL.90).aspx
本文分两部分讲解，首先介绍分区表怎么创建，然后介绍滑动窗口方案，即如何移出旧分区和移入新分区。

一、分区表

1、创建文件组

alter database myDB add filegroup FG1
alter database myDB add filegroup FG2
alter database myDB add filegroup FG3

2、创建分区信息表

create table FilegroupInfo
(
PartitionNumber tinyint,
FilegroupNumber tinyint,
Location nvarchar(50)
)
insert FilegroupInfo values(1,1,N'C:\db')
insert FilegroupInfo values(2,2,N'C:\db')
insert FilegroupInfo values(3,3,N'C:\db')

3、创建文件

declare @PartitionNumber tinyint,@Location nvarchar(50),@ExecStr nvarchar(300)
declare FilegroupsToCreate cursor for
select PartitionNumber,Location from FileGroupInfo order by PartitionNumber
open FilegroupsToCreate
fetch next from FilegroupsToCreate into @PartitionNumber,@Location
while(@@fetch_status<>-1)
begin
if(@@fetch_status<>-2)
begin
select @ExecStr=N'alter database myDB add file'+
N'(name=N''myDBFG'+CONVERT(nvarchar,@PartitionNumber)+N'File'','+
N'filename=N'''+@Location+N'\myDBFG'+CONVERT(nvarchar, @PartitionNumber)+'File.ndf'','+
N'size=1MB,maxsize=100MB,filegrowth=5MB)'+
N'to filegroup FG'+CONVERT(nvarchar, @PartitionNumber)
exec(@ExecStr)
end
fetch next from FilegroupsToCreate into @PartitionNumber,@Location
end
deallocate FilegroupsToCreate

4、创建分区函数

create partition function _disibledevent=> TotalDue money null
)on _disibledevent=>

二、滑动窗口方案

1、确定文件组

select ps.name as PSName,dds.destination_id as PartitionNumber,fg.name as FileGroupName
from (((sys.tables as t inner join sys.indexes as i _disibledevent=> inner join sys.partition_schemes as ps _disibledevent=> inner join sys.destination_data_spaces as dds _disibledevent=> inner join sys.filegroups as fg _disibledevent=>where (t.name='Orders') and (i.index_id in (0,1))
and dds.destination_id = $partition.OneYearDateRangePFN('20020301')

2、创建移入分段表

create table OrdersNew
(
OrderID int not null,
EmployeeID int null,
VendorID int null,
TaxAmt money null,
Freight money null,
SubTotal money null,
Status tinyint not null,
RevisionNumber tinyint null,
ModifiedDate datetime null,
ShipMethodID tinyint null,
ShipDate datetime not null,
OrderDate datetime not null,
TotalDue money null
) _disibledevent=>

4、为移入分段表建立索引

alter table OrdersNew add constraint OrdersNewPK
primary key clustered(OrderDate,OrderID)
on FG1

5、为移入分段表加载数据

insert OrdersNew select
PurchaseOrderID,
EmployeeID,
VendorID,
TaxAmt,
Freight,
SubTotal,
Status,
RevisionNumber,
ModifiedDate,
ShipMethodID,
ShipDate,
OrderDate,
TotalDue
from AdventureWorks.Purchasing.PurchaseOrderHeader where(OrderDate>='20020601' and OrderDate<'20020701')

6、创建移出分段表

create table OrdersOld
(
OrderID int not null,
EmployeeID int null,
VendorID int null,
TaxAmt money null,
Freight money null,
SubTotal money null,
Status tinyint not null,
RevisionNumber tinyint null,
ModifiedDate datetime null,
ShipMethodID tinyint null,
ShipDate datetime not null,
OrderDate datetime not null,
TotalDue money null
) _disibledevent=> alter table Orders drop constraint OrdersRangeYearCK

13、将新数据移入分段表

alter table OrdersNew switch to Orders partition 3

14、删除分段表

drop table OrdersNew
drop table OrdersOld

三、验证

1、查看文件和文件组

exec sp_helpfile

2、查看分区和行数

select $partition.OneYearDateRangePFN(o.OrderDate) as 分区编号,
min(o.OrderDate) as 最小日期,
max(o.OrderDate) as 最大日期,
count(*) as 行数
from Orders as o
group by $partition.OneYearDateRangePFN(o.OrderDate)
order by 分区编号

延伸阅读