SQL普通表转分区表的方法

代码如下: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],'2011-6-1')
insert Sale ([Name],'2011-7-1')
insert Sale ([Name],'2011-8-1')
insert Sale ([Name],'2012-9-1')
insert Sale ([Name],'2012-10-1')
insert Sale ([Name],'2012-11-1')
insert Sale ([Name],'2013-12-1')
insert Sale ([Name],'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,
)on [pt_Sale](SaleTime) --注意这里使用[pt_Sale]架构，根据SaleTime 分区

-- 查看使用情况
SELECT *,$PARTITION.[pt_Sale](SaleTime)
FROM dbo.[AvCache]

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!