SqlServer表分区

一、创建普通表并插入数据准备测试环境

--创建普通表
CREATE TABLE [dbo].[fenquTable](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
    [createTime] [datetime] NULL
    );

ALTER TABLE [dbo].[fenquTable] ADD  CONSTRAINT [PK_fenquTable] PRIMARY KEY NONCLUSTERED 
(
    [id] ASC
)
GO
CREATE CLUSTERED INDEX [CT_fenquTable] ON [dbo].[fenquTable]
(
    [createTime] ASC
)
GO
--插入测试数据
insert into fenquTable(name,createTime) values ('隔壁老王','2010-01-01');
insert into fenquTable(name,createTime) values ('隔壁老张','2011-01-01');
insert into fenquTable(name,createTime) values ('隔壁老赵','2012-01-01');
insert into fenquTable(name,createTime) values ('隔壁老李','2013-01-01');
insert into fenquTable(name,createTime) values ('老李儿子','2013-10-01');
insert into fenquTable(name,createTime) values ('隔壁老田','2014-01-01');
insert into fenquTable(name,createTime) values ('隔壁老梁','2015-01-01');
insert into fenquTable(name,createTime) values ('老梁姑娘楠楠','2015-10-10');



二、准备文件组、文件、分区函数、分区方案
--添加文件组
alter database Baike add filegroup group2013;
alter database Baike add filegroup group2014;
alter database Baike add filegroup group2015;
--添加文件
alter database Baike 
add file(name='web2013',filename='D:\data\web2013.ndf',size=5mb,filegrowth=5mb)
to filegroup group2013;

alter database Baike 
add file(name='web2014',filename='D:\data\web2014.ndf',size=5mb,filegrowth=5mb)
to filegroup group2014;

alter database Baike 
add file(name='web2015',filename='D:\data\web2015.ndf',size=5mb,filegrowth=5mb)
to filegroup group2015;

---分区函数
create partition function fenqu(datetime) --分区函数名
as range right  --right分区方式 边界值去左表还是右表
for values ('2014-01-01','2015-01-01') --按这些值来分区 
--group2013 : 2014-01-01 之前的
--group2014 : 2014-01-01 到 2014-12-31的
--group2015 : 2015-01-01 之后的


--创建分区方案
create partition scheme SchemeFenqu --分区方案名
as partition fenqu    --之前创建的分区函数
to(group2013,group2014,group2015); --之前创建的文件组


三、将普通表转换为分区表
--删除主键，自动同时删除索引
alter table fenquTable drop constraint PK_fenquTable 

--创建主键，但不创建聚集索引
alter table fenquTable add constraint PK_fenquTable
primary key nonclustered --非聚集
(id asc) on [primary];

--然后在createTime字段上创建一个聚集索引
create clustered index CT_fenquTable on fenquTable(createTime)
with(drop_existing=on) --如果存在则删除
on schemeFenqu(createTime); --并调用分区方案

--然后再查询分区，发现数据保留情况下，已经将数据按规则进行分区了
select $partition.fenqu(createTime) as 分区,count(id) as 数量
from fenquTable group by $partition.fenqu(createTime);

--查看分区表明细
select * from fenquTable where $partition.fenqu(createTime)=1;
select * from fenquTable where $partition.fenqu(createTime)=2;
select * from fenquTable where $partition.fenqu(createTime)=3;



四、添加分区

--添加2016年数据
insert into fenquTable (name,createTime) values ('16年小明','2016-03-05');

--创建文件组
alter database Baike add filegroup group2016

--添加数据库文件
alter database Baike 
add file(name='web2016',filename='D:\data\web2016.ndf',size=5mb,filegrowth=5mb)
to filegroup group2016

--修改分区方案
alter partition scheme SchemeFenqu
next used group2016;

--修改分区函数
alter partition function fenqu()
split range('2016-01-01');


--查看分区及统计
select $partition.fenqu(createTime) as 分区,count(id) as 数量
from fenquTable group by $partition.fenqu(createTime);

五、删除分区
--删掉该边界值
alter partition function fenqu() merge range('2014-01-01');

--将指定文件中的所有数据迁移到同一文件组中的其他文件  
DBCC SHRINKFILE ([web2014], EMPTYFILE);  
GO  

--移除文件、文件组
ALTER DATABASE [Baike] REMOVE FILE [web2014]  
GO  
ALTER DATABASE [Baike] REMOVE FILEGROUP [group2014]  
GO  


--再次查询
select $partition.fenqu(createTime) as 分区,count(id) as 数量
from fenquTable group by $partition.fenqu(createTime);

DBCC showfilestats  
GO

六、拆分分区
--创建文件组
alter database Baike add filegroup group2014

--添加数据库文件
alter database Baike add file(name='web2014',filename='D:\data\web2014.ndf',size=5mb,filegrowth=5mb)
to filegroup group2014

--修改分区方案
alter partition scheme SchemeFenqu
next used group2014

--修改分区函数
alter partition function fenqu()
split range('2014-01-01')

--查看分区及统计
select $partition.fenqu(createTime) as 分区,count(id) as 数量
from fenquTable group by $partition.fenqu(createTime);

DBCC showfilestats  
GO

七、分区表转普通表
--修改分区函数 将边界值都删除
--这样虽然只有一个分区了，但是查看数据表存储位置，是否进行分区：True，分区数1.
alter partition function fenqu()merge range('2014-01-01');
alter partition function fenqu()merge range('2015-01-01');
alter partition function fenqu()merge range('2016-01-01');

--查询
select $partition.fenqu(createTime) as 分区,count(id) as 数量
from fenquTable group by $partition.fenqu(createTime);

DBCC showfilestats  
GO


--清空文件
--DBCC SHRINKFILE ('web2013', EMPTYFILE);--重新建立聚集索引后再删
DBCC SHRINKFILE ('web2014', EMPTYFILE);
DBCC SHRINKFILE ('web2015', EMPTYFILE);
DBCC SHRINKFILE ('web2016', EMPTYFILE);


--删除文件
--ALTER DATABASE [Baike] REMOVE FILE [web2013];--重新建立聚集索引后再删
ALTER DATABASE [Baike] REMOVE FILE [web2014];
ALTER DATABASE [Baike] REMOVE FILE [web2015];
ALTER DATABASE [Baike] REMOVE FILE [web2016];

--删除文件组
--ALTER DATABASE [Baike] REMOVE FILEGROUP [group2013];--重新建立聚集索引后再删
ALTER DATABASE [Baike] REMOVE FILEGROUP [group2014];
ALTER DATABASE [Baike] REMOVE FILEGROUP [group2015];
ALTER DATABASE [Baike] REMOVE FILEGROUP [group2016];

--重新建立聚集索引
--删除分区索引后，重新建立聚集索引，这时再此查看数据表的存储位置，是否分区：Flase。
create clustered index CT_fenquTable on fenquTable(createTime)
    with(drop_existing=on) --如果存在则删除
on [primary];


--删除最后一个分区文件\文件组
DBCC SHRINKFILE ('web2013', EMPTYFILE);--最后再删
ALTER DATABASE [Baike] REMOVE FILE [web2013];--最后再删

--删除分区方案
DROP PARTITION SCHEME [schemeFenqu]

--删除分区函数
DROP PARTITION FUNCTION [fenqu]
GO

---删除分区方案后,才能删除成功?
ALTER DATABASE [Baike] REMOVE FILEGROUP [group2013];--最最后再删



--查看
SELECT COUNT(*) FROM fenquTable   
GO   
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]    
FROM sys.indexes i    
INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id    
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]    
WHERE o.name in( 'fenquTable','TestTab2');
GO    
DBCC showfilestats  
GO

--在查询Baike的文件组.
sp_helpdb Baike 

-- 查询该文件组[group2013]下的对象.
select filegroup=s.groupname,
filename=c.name,
tablename=object_Name(i.ID),
indexname=i.name,
indextype=case when i.indid=0 then 'Heap' 
when i.indid=1 then 'Clustered index' 
when i.indid>1 then 'Nonclustered index' end 
from sysfilegroups s, sysindexes i,sysfiles c
where i.groupid=s.groupid and i.groupid=c.groupid
--and s.groupname='group2013'
and object_Name(i.ID)='fengquTable'