-- 创建分区函数 IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'PF_Orders_OrderDateRange') DROP PARTITION FUNCTION PF_Orders_OrderDateRange; GOcreate partition function PF_Orders_OrderDateRange(datetime) as range right for values ( '2006-01-01', '2007-01-01', '2008-01-01' ) go/*ALTER PARTITION FUNCTION PF_Orders_OrderDateRange() SPLIT RANGE ('2009-01-01')*/ -- 创建分区方案 IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'PS_Orders') DROP PARTITION scheme PS_Orders; GOcreate partition scheme PS_Orders as partition PF_Orders_OrderDateRange to ([primary], [primary], [primary], [primary]) go/*ALTER PARTITION SCHEME PS_Orders NEXT USED [primary] */-- 创建分区表 IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Orders' and type = 'U') DROP TABLE Orders GO create table dbo.Orders ( OrderID int not null, OrderDate datetime not null ) on PS_Orders(OrderDate) go-- 创建聚集分区索引 IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IXC_Orders_OrderDate' ) DROP index IXC_Orders_OrderDate on 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, OrderDate) go
IF EXISTS (SELECT * FROM sys.partition_functions
WHERE name = 'PF_Orders_OrderDateRange')
DROP PARTITION FUNCTION PF_Orders_OrderDateRange;
GOcreate partition function PF_Orders_OrderDateRange(datetime)
as
range right for values (
'2006-01-01',
'2007-01-01',
'2008-01-01'
)
go/*ALTER PARTITION FUNCTION PF_Orders_OrderDateRange()
SPLIT RANGE ('2009-01-01')*/
-- 创建分区方案
IF EXISTS (SELECT * FROM sys.partition_schemes
WHERE name = 'PS_Orders')
DROP PARTITION scheme PS_Orders;
GOcreate partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
to ([primary], [primary], [primary], [primary])
go/*ALTER PARTITION SCHEME PS_Orders
NEXT USED [primary] */-- 创建分区表
IF EXISTS (SELECT * FROM sys.objects
WHERE name = 'Orders' and type = 'U')
DROP TABLE Orders
GO
create table dbo.Orders
(
OrderID int not null,
OrderDate datetime not null
)
on PS_Orders(OrderDate)
go-- 创建聚集分区索引
IF EXISTS (SELECT * FROM sys.indexes
WHERE name = 'IXC_Orders_OrderDate' )
DROP index IXC_Orders_OrderDate on 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, OrderDate)
go