这问题今天纠结了我一天了。
下面的代码是网上转载来的
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 不了。
只能出不能进。
