复制代码 代码如下:
create table sale(
[id] [int] identity(1,1) not null, –自动增长
[name] [varchar](16) not null,
[saletime] [datetime] not null,
constraint [pk_sale] primary key clustered –创建主键
(
[id] asc
)
)
–插入一些记录
insert sale ([name],[saletime]) values (‘张三’,’2009-1-1′)
insert sale ([name],[saletime]) values (‘李四’,’2009-2-1′)
insert sale ([name],[saletime]) values (‘王五’,’2009-3-1′)
insert sale ([name],[saletime]) values (‘钱六’,’2010-4-1′)
insert sale ([name],[saletime]) values (‘赵七’,’2010-5-1′)
insert sale ([name],[saletime]) values (‘张三’,’2011-6-1′)
insert sale ([name],[saletime]) values (‘李四’,’2011-7-1′)
insert sale ([name],[saletime]) values (‘王五’,’2011-8-1′)
insert sale ([name],[saletime]) values (‘钱六’,’2012-9-1′)
insert sale ([name],[saletime]) values (‘赵七’,’2012-10-1′)
insert sale ([name],[saletime]) values (‘张三’,’2012-11-1′)
insert sale ([name],[saletime]) values (‘李四’,’2013-12-1′)
insert sale ([name],[saletime]) values (‘王五’,’2014-12-1′)
alter table sale drop constraint pk_sale
–创建主键，但不设为聚集索引
alter table sale add constraint pk_sale primary key nonclustered
(
[id] asc
) on [primary]
–创建分区函数
go
create partition function [pf_sale](saletime) as range left for values(n’2010-5-1t00:00:00.000′, n’2012-9-1t00:00:00.000′
go
–创建分区结构
go
create partition scheme [pt_sale] as partition [pf_sale] to ([sale1], [sale3], [sale2])
go
–创建一个使用[pt_sale]架构
go
create table [dbo].[avcache](
[id] [int] identity(1,1) not null, –自动增长
[name] [varchar](16) not null,
[saletime] [datetime] not null,
)on [pt_sale](saletime) –注意这里使用[pt_sale]架构，根据saletime 分区
— 查看使用情况
select *, $partition.[pt_sale](saletime)
from dbo.[avcache]