复制代码 代码如下:

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]