--表分区分为以下个步骤
--********************1、创建文件组***********************
-- 创建文件组语法
-- alter database <数据库名> add filegroup <文件组名>
alter database test_1206 add filegroup GroupOne
alter database test_1206 add filegroup GroupTwo
alter database test_1206 add filegroup GroupThree
alter database test_1206 add filegroup GroupFour
alter database test_1206 add filegroup GroupFive
--2.创建数据文件到指定文件组(好处:※提高IO性能 --- 文件组中的文件可以位于不同的磁盘,多磁盘协同工作,提升性能。)**********************
-- 创建数据文件到指定文件组语法
-- alter database <数据库名称> add file <文件属性> to filegroup <文件组名称>
/**文件属性>
(
name=文件的逻辑名称,
filename=文件的物理名称,
size=文件初始大小,
filegrowth=文件自动增长量(数值或百分比),
maxsize=文件增长的最大值
)
复制代码
复制代码**/
alter database test_1206 add file
(
name=N'OneFile',
filename=N'E:\DataDB\OneFile.mdf',
size=3MB,
filegrowth=10%,
maxsize=unlimited -- 无限大小
)
to filegroup GroupOne
alter database test_1206 add file
(
name=N'TwoFile',
filename=N'E:\DataDB\TwoFile.mdf',
size=3MB,
filegrowth=10%,
maxsize=unlimited -- 无限大小
)
to filegroup GroupTwo
alter database test_1206 add file
(
name=N'ThreeFile',
filename=N'E:\DataDB\ThreeFile.mdf',
size=3MB,
filegrowth=10%,
maxsize=unlimited -- 无限大小
)
to filegroup GroupThree
alter database test_1206 add file
(
name=N'FourFile',
filename=N'E:\DataDB\FourFile.mdf',
size=3MB,
filegrowth=10%,
maxsize=unlimited -- 无限大小
)
to filegroup GroupFour
alter database test_1206 add file
(
name=N'FiveFile',
filename=N'E:\DataDB\FiveFile.mdf',
size=3MB,
filegrowth=10%,
maxsize=unlimited -- 无限大小
)
to filegroup GroupFive
--3.创建分区函数(创建一个分区函数,创建分区函数的目的是告诉 SQL Server 以什么方式对分区表进行分区。)*************************************
/**
create partition function -- 创建分区函数
Part_Fun(int) -- 分区函数名(分区列类型)
as range [left/right] -- 左置/右置,即边界值的存储位置,如果设置为右置,边界值存到下一个表
for values ('1000','2000','3000','4000','5000') -- 设置每个分区表的边界值
**/
--案例
create partition function
Part_Fun(int)
as range right
for values ('1000','2000','3000','4000','5000')
/**
删除分区函数:
--删除分区函数语法
drop partition function <分区函数名>
--删除名为 Part_Fun 的分区函数
drop partition function Part_Fun
**/
--※ PS:只有当分区函数没有应用到分区方案中时,指定的分区函数才能被删除。
--4.创建分区方案(分区方案的作用是将分区函数生成的分区映射到文件组中去。分区函数的作用是告诉SQL Server,如何将数据进行分区,而分区方案的作用则是告诉 SQL Server 将已分区的数据放在哪个文件组中。)***************************************
--创建分区方案语法
/**
create partition scheme -- 创建分区方案
<分区方案名称> -- 分区方案名称
as partition <分区函数名称> -- 指定分区函数名称
to (文件组名称,,,,) -- 指定分区函数划分出来的数据对应存放的文件组
**/
create partition scheme -- 创建分区方案
Part_Plan -- 分区方案名称
as partition Part_Fun -- 分区函数名称
to ('GroupOne','GroupTwo','GroupThree','GroupFour','GroupFive') -- 分区文件组
/**
不对呀,我明明建立的是五个分区文件组,分区函数也是分为五份的啊。
其实这里的意思应该就是后续数据的问题了,首先不可能保证这个表永远就 5000 条数据的,所以他在这里的意思就是后续数据存储的文件组。这里我把后续数据放在最后一个文件组里面。**/
create partition scheme -- 创建分区方案
Part_Plan -- 分区方案名称
as partition Part_Fun -- 分区函数名称
to ('GroupOne','GroupTwo','GroupThree','GroupFour','GroupFive','GroupFive') -- 分区文件组
/**
删除分区方案
--删除分区方案语法
drop partition scheme<分区方案名称>
--删除名为 Part_Plan 的分区方案
drop partition scheme Part_Plan
PS:当没有分区表引用该分区方案时,才能对其进行删除。
**/
-- 5.创建分区表
--创建分区表语法
/**
create table <表名> -- 表名称
(
column1 int not null primary key nonclustered, -- 字段名称、字段类型、是否可空、主键约束、非聚集索引
column2 int not null
) on <分区方案名>(分区列名) -- 分区方案的名称(指定要依据分区的列名)
**/
create table US_Info
(
ID int not null primary key identity(1,1),
Name nvarchar(32) null,
CreateTime nvarchar(32) null
)on Part_Plan(ID)
-- PS:※如果在表中创建有主键、唯一索引、聚集索引,则分区依据列必须为该列之一。即分区依据列必须建立在主键、唯一索引、聚集索引之上。
-- 创建分区索引:
/**
create [ unique [ clustered | nonclustered ] ] -- unique 唯一 clustered 聚集 nonclustered 非聚集
index <索引名称> -- 指定索引名称
on <表名>(列名) -- 指定表名(指定列名)
on <分区方案名>(分区依据列名) -- 分区方案名称(分区依据列名)
**/
create nonclustered
index Part_Non_Name
on US_Info(Name)
on Part_Plan(ID)
-- 在表 US_Info 中插入5000条数据:
declare @I int
set @I=1
while(@I<=5000)
begin
insert into US_Info(Name,CreateTime)
values('名称'+convert(nvarchar,@I),Convert(nvarchar,GETDATE(),121))
set @I=@I+1
end
select * from US_Info
--查询指定值位于数据表哪个分区中:
select $partition.Part_Fun('2999') -- 返回 4,表示位于第四个分区中
select $partition.Part_Fun('3000') -- 返回 4,表示位于第四个分区中
select $partition.Part_Fun('5000') -- 返回 6
--查询分区表中,每个分区存在的数据的行数:
select $partition.Part_Fun(ID) as Part_Num,count(1) as R_Count
from US_Info
group by $partition.Part_Fun(ID)
--查询指定分区中的数据
select * from US_Info
where $partition.Part_Fun(ID)=3
-------------------------------------------------------------------------------------------------------------------------