SQL Server 2005 ·ÖÇø±íʵ¼ù——·ÖÇøÇл»

±¾ÎÄÑÝʾÁË SQL Server 2005 ·ÖÇø±í·ÖÇøÇл»µÄÈýÖÖÐÎʽ£º
1. Çл»·ÖÇø±íµÄÒ»¸ö·ÖÇøµ½ÆÕͨÊý¾Ý±íÖУºPartition to Table£»
2. Çл»ÆÕͨ±íÊý¾Ýµ½·ÖÇø±íµÄÒ»¸ö·ÖÇøÖУºTable to Partition£»
3. Çл»·ÖÇø±íµÄ·ÖÇøµ½ÁíÒ»·ÖÇø±í£ºPartition to Partition¡£
²¢Ö¸³öÁËÔÚ·ÖÇø±í·ÖÇøÇл»¹ý³ÌÖеÄ×¢ÒâÊÂÏî¡£
-- ´´½¨·ÖÇøº¯Êý
create partition function PF_Orders_OrderDateRange(datetime)
as
range right for values (
'1997-01-01',
'1998-01-01',
'1999-01-01'
)
go
-- ´´½¨·ÖÇø·½°¸
create partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
to ([primary], [primary], [primary], [primary])
go
-- ´´½¨·ÖÇø±í
create table dbo.Orders
(
OrderID int not null
,CustomerID varchar(10) not null
,EmployeeID int not null
,OrderDate datetime not null
)
on PS_Orders(OrderDate)
go
-- ´´½¨¾Û¼¯·ÖÇøË÷Òý
create clustered index IXC_Orders_OrderDate on dbo.Orders(OrderDate)
go
-- Ϊ·ÖÇø±íÉèÖÃÖ÷¼ü
alter table dbo.Orders add constraint PK_Orders
primary key (OrderID, CustomerID, OrderDate)
go
-- µ¼ÈëÊý¾Ýµ½·ÖÇø±í
insert into dbo.Orders
select OrderID, CustomerID, EmployeeID, OrderDate
from dbo.Orders_from_SQL2000_Northwind --£¨×¢£ºÊý¾ÝÀ´Ô´ÓÚ SQL Server 2000 ʾÀýÊý¾Ý¿â£©
go
-- ²é¿´·ÖÇø±íÿ¸ö·ÖÇøµÄÊý¾Ý·Ö²¼Çé¿ö
select partition = $partition.PF_Orders_OrderDateRange(OrderDate)
,rows = count(*)
,minval = min(OrderDate)
,maxval = max(OrderDate)
from dbo.Orders
group by $partition.PF_Orders_OrderDateRange(OrderDate)
order by partition
go
Ò»¡¢Çл»·ÖÇø±íµÄÒ»¸ö·ÖÇøµ½ÆÕͨÊý¾Ý±íÖУºPartition to Table
Ê×ÏȽ¨Á¢ÆÕͨÊý¾Ý±í Orders_1998£¬¸Ã±íÓÃÀ´´æ·Å¶©µ¥ÈÕÆÚΪ 1998 ÄêµÄËùÓÐÊý¾Ý¡£
create table dbo.Orders_1998
(
OrderID int not null
,CustomerID varchar(10) not null
,EmployeeID int not null
,OrderDate datetime not null
) on [primary]
go
create clustered index IXC_Orders1998_OrderDate on dbo.Orders_1998(OrderDate)
go
alter table dbo.Orders_1998 add constrai