The method of SQL normal table _mssql partition table

Copy Code code as follows:

CREATE TABLE Sale (
[ID] [int] identity (1,1) not NULL--automatic growth
[Name] [varchar] () not NULL,
[Saletime] [DateTime] Not NULL,
constraint [Pk_sale] PRIMARY key clustered--Create primary key
(
[Id] ASC
)
)
--Insert Some records
Insert Sale ([name],[saletime]) VALUES (' John ', ' 2009-1-1 ')
Insert Sale ([name],[saletime]) VALUES (' Dick ', ' 2009-2-1 ')
Insert Sale ([name],[saletime]) VALUES (' Harry ', ' 2009-3-1 ')
Insert Sale ([name],[saletime]) VALUES (' Money six ', ' 2010-4-1 ')
Insert Sale ([name],[saletime]) values (' Zhao Qi ', ' 2010-5-1 ')
Insert Sale ([name],[saletime]) VALUES (' John ', ' 2011-6-1 ')
Insert Sale ([name],[saletime]) VALUES (' Dick ', ' 2011-7-1 ')
Insert Sale ([name],[saletime]) VALUES (' Harry ', ' 2011-8-1 ')
Insert Sale ([name],[saletime]) VALUES (' Money six ', ' 2012-9-1 ')
Insert Sale ([name],[saletime]) values (' Zhao Qi ', ' 2012-10-1 ')
Insert Sale ([name],[saletime]) VALUES (' John ', ' 2012-11-1 ')
Insert Sale ([name],[saletime]) VALUES (' Dick ', ' 2013-12-1 ')
Insert Sale ([name],[saletime]) VALUES (' Harry ', ' 2014-12-1 ')

ALTER TABLE Sale DROP constraint Pk_sale
--Creates a primary key, but is not set to a clustered index
Alter TABLE Sale add constraint Pk_sale primary key nonclustered
(
[ID] ASC
) on [PRIMARY]

--Create partition functions
Go
create partition function [Pf_sale] (saletime) as range left for values (n ' 2010-5-1t00:00:00.000 ', n ' 2012-9-1t00:0 0:00.000 '
Go

--Create a partition structure
Go
create partition scheme [Pt_sale] as partition [Pf_sale] to ([Sale1], [Sale3], [Sale2])
Go

--Create a using [Pt_sale] Schema
Go
CREATE TABLE [dbo]. [Avcache] (
[ID] [int] identity (1,1) not NULL--automatic growth
[Name] [varchar] () not NULL,
[Saletime] [DateTime] Not NULL,
On [Pt_sale] (saletime)--note that the [Pt_sale] schema is used here, depending on the saletime partition

--View Usage
SELECT *, $PARTITION. [Pt_sale] (Saletime)
FROM dbo. [Avcache]