SQL Server 2005分区表学习笔记

参考自：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 OneYearDateRangePFN(datetime) as
range left for values('20020331 23:59:59.997',
            '20020430 23:59:59.997',
            '20020531 23:59:59.997')

5、创建分区架构

create partition scheme OneYearDateRangePScheme as
partition OneYearDateRangePFN to(FG1,FG2,FG3,[PRIMARY])

6、创建表

create table Orders
(
    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 constraint OrdersRangeYearCK check(OrderDate>='20020301' and OrderDate<'20020601'),
    TotalDue money null
)on OneYearDateRangePScheme(OrderDate)

7、创建索引

alter table Orders
add constraint OrdersPK
    primary key clustered(OrderDate, OrderID)
    on OneYearDateRangePScheme(OrderDate)

8、加载数据

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

二、滑动窗口方案 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 on (t.object_id=i.object_id))
    inner join sys.partition_schemes as ps on (i.data_space_id=ps.data_space_id))
    inner join sys.destination_data_spaces as dds on (ps.data_space_id=dds.partition_scheme_id))
    inner join sys.filegroups as fg on dds.data_space_id=fg.data_space_id
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
) on FG1

3、为移入分段表建立约束

alter table OrdersNew with check
add constraint OrdersNewRangeYearCK
    check(OrderDate>='20020601' and OrderDate<='20020630 23:59:59.997')

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
) on FG1

7、为移出分段表建立索引

alter table OrdersOld add constraint OrdersOldPK
    primary key clustered(OrderDate,OrderID)
on FG1

8、将旧数据移出分区表

alter table Orders switch partition 1 to OrdersOld

9、删除旧边界

alter partition function OneYearDateRangePFN()
merge range('20020331 23:59:59.997')

10、设置下一个使用的分区

alter partition scheme OneYearDateRangePScheme next used FG1

11、添加新边界

alter partition function OneYearDateRangePFN() split range('20020630 23:59:59.997')

12、更改约束

alter table Orders add constraint OrdersRangeMax
    check(OrderDate<'20020701')

alter table Orders add constraint OrdersRangeMin
    check(OrderDate>='20020401')

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 分区编号