SQL普通表转分区表的方法
程序员文章站
2023-12-10 23:48:46
复制代码 代码如下:create table sale( [id] [int] identity(1,1) not nul...
复制代码 代码如下:
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]