分区表（both in Sql005 & Sql2008）

AS Range Right
For Values(0,1000000)
--建立分区方案
--定义数据的每个分段的存储目标
Create Partition Scheme ps_ByDate
AS Partition pf_ByDate
To (HistoryData,CurrentData,CurrentData)

--建立表按照分区方案存储
Create table Orders_New
(Orderid int Identity,
 About Nchar(500),
 Orderdate Datetime)
On ps_ByDate(Orderdate)

--对齐的索引
Create Index CX_Orderid
On Orders_New(Orderid)
On ps_ByDate(Orderdate)

--建立历史数据归档表按照分区方案存储
Create table Orders_History
(Orderid int Identity,
 About Nchar(500),
 Orderdate Datetime)
On ps_ByDate(Orderdate)

--通过数据文件的增长观察数据的存放
Declare @n int
Set @n=0
While @n<1000
Begin
 Insert Orders_New Values
    ('aaaaaaaaa','2004-5-1')
 Set @n=@n+1
End

Select * from Orders_New
Select * from Orders_History

--移动数据到相同结构的归档表
Alter Table Orders_New
Switch Partition 1
To Orders_History Partition 1
--合并早前的分区
Alter Partition Function pf_ByDate()
Merge Range('2005-1-1')
--指定新分区的目标文件组
Alter Partition Scheme ps_ByDate
Next Used CurrentData
--在2008年分裂出新的分区
Alter Partition Function pf_ByDate()
Split Range ('2008-1-1')

--查看每个分区中的记录数
SELECT $PARTITION.pf_ByDate(OrderDate)
          AS Partition,COUNT(*) AS [COUNT]
FROM dbo.Orders_New
GROUP BY $PARTITION.pf_ByDate(OrderDate)
ORDER BY Partition ;
Go
--查看每个分区中的记录数
SELECT $PARTITION.pf_ByDate(OrderDate)
         AS Partition,COUNT(*) AS [COUNT]
FROM dbo.Orders_History
GROUP BY $PARTITION.pf_ByDate(OrderDate)
ORDER BY Partition ;
GO

--为不符合分区条件的表建立分区条件
Create table OrderDetials
(OrderId int,
 ProductId int,
 Unitprice money,
 Quantity int)

Alter table OrderDetials
Add OrderDate DateTime

Update OrderDetials
 Set OrderDate = Orders_New.OrderDate
 From OrderDetials,Orders_New
 Where OrderDetials.OrderId=Orders_New.OrderId