- --删除原来的数据表
- drop
table Sale
- --新建一个普通的数据表
- 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]
- --创建一个新的聚集索引,在该聚集索引中使用分区方案
- CREATE
CLUSTERED INDEX CT_Sale ON Sale([SaleTime]) - ON
partschSale([SaleTime])
- --统计所有分区表中的记录总数
- select
$PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime)