这问题今天纠结了我一天了。
下面的代码是网上转载来的
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
-- 创建分区表
sp_rename 'dbo.Orders','Orders_From_SQL2000_Northwind'
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
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 constraint PK_Orders_1998
primary key nonclustered (OrderID, CustomerID, OrderDate)
go
alter table dbo.Orders switch partition 3 to dbo.Orders_1998
go
alter table dbo.Orders_1998 add constraint CK_Orders1998_OrderDate
check (OrderDate>='1998-01-01' and OrderDate<'1999-01-01')
go
alter table dbo.Orders_1998 switch to dbo.Orders partition 3
这段代码是没问题的，普通表能成功的switch 分区表。但是有一点要注意：分区字段为 not null。如果你改为null 不好意思 switch 不了。
只能出不能进。