SqlServer按字母分区表

最新推荐文章于 2024-06-04 16:22:13 发布

马立弘

最新推荐文章于 2024-06-04 16:22:13 发布

阅读量786

点赞数 1

CC 4.0 BY-SA版权

分类专栏： MSSQL 文章标签： sql server

本文链接：https://blog.youkuaiyun.com/manimanihome/article/details/53154017

MSSQL 专栏收录该内容

39 篇文章

订阅专栏

本文详细介绍如何在 SqlServer 中创建按字母顺序分区的表，包括表创建、数据插入、分区函数与方案设置、普通表转分区表及反向操作等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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];--最最后再删