SqlServer按字母分区表

SqlServer按字母分区

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

ALTER TABLE [dbo].[emailTable] ADD  CONSTRAINT [PK_emailTable] PRIMARY KEY NONCLUSTERED 
(
    [id] ASC
)
GO
CREATE CLUSTERED INDEX [CT_emailTable] ON [dbo].[emailTable]
(
    [email] ASC
)
GO
--插入测试数据
insert into emailTable(email,createTime) values ('bile@163.com','2010-01-01');
insert into emailTable(email,createTime) values ('b','2010-01-01');
insert into emailTable(email,createTime) values ('aliph@163.com','2011-01-01');
insert into emailTable(email,createTime) values ('aq233s@163.com','2012-01-01');
insert into emailTable(email,createTime) values ('cillli@163.com','2013-01-01');
insert into emailTable(email,createTime) values ('afdsf@116.com','2013-10-01');
insert into emailTable(email,createTime) values ('dd@163.com','2014-01-01');
insert into emailTable(email,createTime) values ('Aaaaa@163.com','2015-01-01');
insert into emailTable(email,createTime) values ('01b@163.com','2015-10-10');
insert into emailTable(email,createTime) values ('中国@163.com','2015-10-10');


二、准备文件组、文件、分区函数、分区方案
--添加文件组
alter database Baike add filegroup group0;
alter database Baike add filegroup groupa;
alter database Baike add filegroup groupb;
alter database Baike add filegroup groupc;
--添加文件

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

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

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

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

---分区函数
create partition function fenqu(nvarchar(50)) --分区函数名
as range right  --right分区方式 边界值去左表还是右表
for values ('a','b','c') --按这些值来分区 
--groupa : a 之前的
--groupb : a 到 b的
--groupc : b 之后的


--创建分区方案
create partition scheme SchemeFenqu --分区方案名
as partition fenqu    --之前创建的分区函数
to(group0,groupa,groupb,groupc); --之前创建的文件组


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

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

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

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

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


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

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

DBCC showfilestats  
GO


--清空文件
--DBCC SHRINKFILE ('email0', EMPTYFILE);--重新建立聚集索引后再删
DBCC SHRINKFILE ('emaila', EMPTYFILE);
DBCC SHRINKFILE ('emailb', EMPTYFILE);
DBCC SHRINKFILE ('emailc', EMPTYFILE);


--删除文件
--ALTER DATABASE [Baike] REMOVE FILE [email0];--重新建立聚集索引后再删
ALTER DATABASE [Baike] REMOVE FILE [emaila];
ALTER DATABASE [Baike] REMOVE FILE [emailb];
ALTER DATABASE [Baike] REMOVE FILE [emailc];

--删除文件组
--ALTER DATABASE [Baike] REMOVE FILEGROUP [group0];--重新建立聚集索引后再删
ALTER DATABASE [Baike] REMOVE FILEGROUP [groupa];
ALTER DATABASE [Baike] REMOVE FILEGROUP [groupb];
ALTER DATABASE [Baike] REMOVE FILEGROUP [groupc];

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


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

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

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

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