[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 行受影响)
*/