[sql server] SQL Server 2005中的分区表

SQL Server 2005中的分区表（一）：什么是分区表？为什么要用分区表？如何创建分区表？

SQL Server 2005中的分区表（二）：如何添加、查询、修改分区表中的数据

SQL Server 2005中的分区表（三）：将普通表转换成分区表

SQL Server 2005中的分区表（四）：删除（合并）一个分区

SQL Server 2005中的分区表（五）：添加一个分区

SQL Server 2005中的分区表（六）：将已分区表转换成普通表

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sale]') AND type in (N'U'))
DROP TABLE [dbo].[Sale]
go

IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'partschSale')
DROP PARTITION SCHEME [partschSale]
go

IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'partfunSale')
DROP PARTITION FUNCTION [partfunSale]
go

--创建分区函数
CREATE PARTITION FUNCTION partfunSale (datetime)
AS RANGE RIGHT FOR VALUES ('20100101','20110101','20120101','20130101')
go

--创建分区方案
CREATE PARTITION SCHEME partschSale
AS PARTITION partfunSale
ALL TO ([PRIMARY] )
go

--创建分区表
CREATE TABLE Sale(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](16) NOT NULL,
[SaleTime][datetime] NOT NULL
) ON partschSale([SaleTime])
go

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')
go

select * from Sale
--
select $PARTITION.partfunSale ('2010-10-1')
/*
-----------
2
*/

select * from Sale where $PARTITION.partfunSale(SaleTime)=1
select * from Sale where $PARTITION.partfunSale(SaleTime)=2
select * from Sale where $PARTITION.partfunSale(SaleTime)=3
select * from Sale where $PARTITION.partfunSale(SaleTime)=4
select * from Sale where $PARTITION.partfunSale(SaleTime)=5

/*
Id Name SaleTime
----------- ---------------- -----------------------
1 张三 2009-01-01 00:00:00.000
2 李四 2009-02-01 00:00:00.000
3 王五 2009-03-01 00:00:00.000

(3 行受影响)

Id Name SaleTime
----------- ---------------- -----------------------
4 钱六 2010-04-01 00:00:00.000
5 赵七 2010-05-01 00:00:00.000

(2 行受影响)

Id Name SaleTime
----------- ---------------- -----------------------
6 张三 2011-06-01 00:00:00.000
7 李四 2011-07-01 00:00:00.000
8 王五 2011-08-01 00:00:00.000

(3 行受影响)

Id Name SaleTime
----------- ---------------- -----------------------
9 钱六 2012-09-01 00:00:00.000
10 赵七 2012-10-01 00:00:00.000
11 张三 2012-11-01 00:00:00.000

(3 行受影响)

Id Name SaleTime
----------- ---------------- -----------------------
12 李四 2013-12-01 00:00:00.000
13 王五 2014-12-01 00:00:00.000

(2 行受影响)

*/

select $PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数
from Sale group by $PARTITION.partfunSale(SaleTime)

/*
分区编号 记录数
----------- -----------
1 3
2 2
3 3
4 3
5 2

(5 行受影响)

*/

--统计所有分区表中的记录总数
select $PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime)
--修改编号为1的记录，将时间改为2019年1月1日
update Sale set SaleTime='2019-1-1' where id=1
--重新统计所有分区表中的记录总数
select $PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime)

/*
分区编号 记录数
----------- -----------
1 3
2 2
3 3
4 3
5 2

(5 行受影响)

(1 行受影响)

分区编号 记录数
----------- -----------
1 2
2 2
3 3
4 3
5 3

(5 行受影响)

*/

--统计所有分区表中的记录总数
select $PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime)
--原来的分区函数是将2010-1-1之前的数据放在第1个分区表中，将2010-1-1至2011-1-1之间的数据放在第2个分区表中
--现在需要将2011-1-1之前的数据都放在第1个分区表中，也就是将第1个分区表和第2个分区表中的数据合并
--修改分区函数
ALTER PARTITION FUNCTION partfunSale()
MERGE RANGE ('20100101')
--统计所有分区表中的记录总数
select $PARTITION.partfunSale(SaleTime) as 分区编号,count(id) as 记录数 from Sale group by $PARTITION.partfunSale(SaleTime)

/*
分区编号 记录数
----------- -----------
1 2
2 2
3 3
4 3
5 3

(5 行受影响)

分区编号 记录数
----------- -----------
1 4
2 3
3 3
4 3

(4 行受影响)
*/