共计 7577 个字符，预计需要花费 19 分钟才能阅读完成。
这篇文章主要讲解了“SqlServer 关于分区表的相关知识点有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“SqlServer 关于分区表的相关知识点有哪些”吧！
创建分区表的大致步骤
1、建立文件组(类似 oracle 的表空间)，当然不建立也行，把所有分区都放一个文件组内也可以
2、建立分区函数，数据按什么范围分配
3、建立分区方案，关联分区函数，也会关联文件组，分区函数把数据分了几个范围，就需要关联几个文件组，当然也可以把这几个分区范围都放入到同一个文件组
4、建立表，关联分区方案
遇到的一个 Bug
直接右键表导出表结构时导不出分区信息，只能右键数据库 – 任务 – 生成脚本才能导出表的分区信息
分区表的一些结论：
1、分区字段不一定需要建立索引
2、分区字段可以创建为 clustered 索引或 noclustered 索引
3、分区字段不管是 clustered 索引还是 noclustered 索引，重建为 clustered 索引且没有关联分区方案时，分区表就变成了非分区表
4、普通表转换为分区表，只要在该表创建一个 clustered 索引，并在 clustered 索引上使用分区方案即可。比如非分区表的字段 1 创建 clustered 索引且关联分区方案时，该表转换为分区表，且分区字段为字段 1。
5、分区表转换为普通表，如果有分区字段有索引则重建分区字段为 clustered 索引且不关联分区方案即可，分区字段没有索引的话则分区字段新建 clustered 索引且不关联分区方案即可
6、普通表改成分区表或把分区表改成普通表，只能使用 clustered 索引来实现，因为有了 clustered 索引就是索引组织表，通过 clustered 索引的重建来实现表的重新分布。普通表变成分区表，把分区字段重建为 clustered 索引并关联分区方案即可，分区表变成普通表，把分区字段重建为 clustered 索引不要关联分区方案即可。
7、分区表创建唯一性约束，必须包含分区列
8、创建分区方案时，必须保证文件组数量匹配分区函数的分区范围段，文件组名称重复没有关系，当然也可以使用 ALL，指定一个文件组名称，这样所有的分区函数的分区范围段数据都落到这一个文件组。
9、分区函数和分区方案是在一个个数据库里面的，而不是面对整个实例的
10、分区表太大占用很多磁盘空间，delete 了一些字段后大小还是没变，这个时候进行分区合并或把分区表转换为普通表，则大小会降下来
创建分区表的步骤
1.1、建立文件组的示例
alter database test1 add filegroup part1;
alter database test1 add filegroup part1000;
alter database test1 add filegroup part2000;
alter database test1 add filegroup part3000;
alter database test1 add filegroup part4000;
1.2、建立文件的示例，关联文件组
ALTER DATABASE test1 ADD FILE(NAME = test1part1,FILENAME = G:\test1part1.ndf ,SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part1;
ALTER DATABASE test1 ADD FILE(NAME = test1part1000,FILENAME = G:\test1part1000.ndf ,SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part1000;
ALTER DATABASE test1 ADD FILE(NAME = test1part2000,FILENAME = G:\test1part2000.ndf ,SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part2000;
ALTER DATABASE test1 ADD FILE(NAME = test1part3000,FILENAME = G:\test1part3000.ndf ,SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part3000;
ALTER DATABASE test1 ADD FILE(NAME = test1part4000,FILENAME = G:\test1part4000.ndf ,SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part4000;
2、建立分区函数的示例，分区函数名为 partfun1
CREATE PARTITION FUNCTION partfun1 (int)
AS RANGE LEFT FOR VALUES (1000 , 2000 , 3000 , 4000)
–VALUES (1000 , 2000 , 3000 , 4000)表明，将把表分为 5 个区了，是从根据表字段的值的大小来分区，五个区分别是最小 –1000,1000-2000,2000-3000,3000-4000,4000- 最大
3、建立方案的例子, 关联分区函数 partfun1，关联文件组
CREATE PARTITION SCHEME partschema1
AS PARTITION partfun1
TO (part1,part1000,part2000,part3000,part4000);
– 建立在 part1,part1000,part2000,part3000,part4000 几个文件组上
CREATE PARTITION SCHEME partschema2
AS PARTITION partfun1
TO (part1,[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY]);
– 建立在 part1、[PRIMARY]文件组上，把 part1 换成 [PRIMARY] 也没问题，这样就类似都建立在 [PRIMARY] 文件组上
CREATE PARTITION SCHEME partschema3
AS PARTITION partfun1
ALL TO (part1);
– 都建立在 part1 文件组上
CREATE PARTITION SCHEME partschema4
AS PARTITION partfun1
ALL TO ([PRIMARY]);
– 都建立在 [PRIMARY] 文件组上
4、建立分区表的示例
CREATE TABLE parttable1(
[ID] [int] NOT NULL,
[IDText] [nvarchar](max) NULL,
[Date] [datetime] NULL)
ON [partschema1](ID);
insert into parttable1 values (1, 1 ,getdate()-4);
insert into parttable1 values (1001, 1001 ,getdate()-3);
insert into parttable1 values (2001, 2001 ,getdate()-2);
insert into parttable1 values (3001, 3001 ,getdate()-1);
insert into parttable1 values (4001, 4001 ,getdate());
5、验证分区表的数据
SELECT * FROM parttable1;
– 返回分区表所有行
SELECT distinct $PARTITION.[partfun1](4) FROM parttable1;
– 返回 ID 字段值为 4 的行属于哪个分区
SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)=2
– 返回第 2 个分区的所有行,ID 就是分区字段 ID
注意：不能因为 SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)= 2 有结果就说明它是分区表，本文最后试验 7 该表是非分区表了，但是执行 SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)= 2 还是有结果的
新增分区
1、为分区方案指定一个可以使用的文件组(新增分区方案的文件组)。
2、修改分区函数(新增分区函数的数据范围)
ALTER PARTITION SCHEME partschema1 NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION partfun1() SPLIT RANGE ( 4500)
select p.partition_number,p.rows from sys.indexes i inner join sys.partitions p on p.object_id = i.object_id and i.object_id = object_id(parttable2) order by 1
– 第一条语句，如果分区方案使用的 ALL TO ([PRIMARY])，则这条语句不用执行
– 第二条语句新增一个分区，范围是 4000-4500
– 第三条语句验证新增分区是否存在，是否存在行数
删除 \ 合并分区
ALTER PARTITION FUNCTION partfun1() MERGE RANGE ( 2000)
就把 1000-2000 这个分区，删除了，合并成了 1000-3000
– 无法像 oracle 一样执行 ALTER TABLE TABLENAME DROP PARTITION PARTITIONNAME;
删除分区表及对应的文件组
删除顺序为：删除分区表、删除分区方案、删除分区函数，最后删除文件组，删除完文件组后对应的文件也就删除了
分区表转换为普通表，普通表转换为分区表的示例
DROP TABLE parttable1;
CREATE TABLE parttable1(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](16) NOT NULL,
[Id2][int] NOT NULL
) ON partschema1(Id2);
insert into parttable1 values (1 ,1);
insert into parttable1 values (1001 ,1001);
insert into parttable1 values (2001 ,2001);
insert into parttable1 values (3001 ,3001);
insert into parttable1 values (4001 ,4001);
1、在分区表上创建的唯一约束，必须包含分区列。
ALTER TABLE parttable1 ADD CONSTRAINT PK_prattable1_id PRIMARY KEY CLUSTERED ([ID] ASC)
报错 Column Id2 is partitioning column of the index PK_prattable1_id . Partition columns for a unique index must be a subset of the index key.
2、分区列 id2 新建 clustered 索引，parttable1 还是分区表
create clustered index CI_prattable1_id2 on parttable1(id2);
3、分区列 id2 创建 nonclustered 索引，parttable1 还是分区表
drop index CI_prattable1_id2 on parttable1;
create nonclustered index NCI_prattable1_id2 on parttable1(id2);
4、非分区列 id 列创建 clustered 索引，parttable1 还是分区表，说明非分区列可以是 cluster 索引列
create clustered index CI_prattable1_id on parttable1(id);
5、分区列 id2 重建为 nonclustered 索引并且不使用分区方案，parttable1 还是分区表
create nonclustered index NCI_prattable1_id2 on parttable1(id2) WITH (DROP_EXISTING = ON) ON [PRIMARY];
6、分区列 id2 重建为 clustered 索引不加 ON 条件，parttable1 还是分区表
drop index CI_prattable1_id on parttable1;
drop index NCI_prattable1_id2 on parttable1;
create clustered index CI_prattable1_id2 on parttable1(id2);
create clustered index CI_prattable1_id2 on parttable1(id2) WITH (DROP_EXISTING = ON);
7、分区列 id2 重建为 clustered 索引加上 ON 条件但不使用分区方案，parttable1 变成了非分区表
create clustered index CI_prattable1_id2 on parttable1(id2) WITH (DROP_EXISTING = ON) ON [PRIMARY];
8、分区列 id2 重建为 clustered 索引并且使用分区方案，parttable1 变成了分区表
create clustered index CI_prattable1_id2 on parttable1(id2) WITH (DROP_EXISTING = ON) on partschema1(Id2);
9、删掉上面 8 的 clustered 索引后，parttable1 还是分区表
drop index CI_prattable1_id2 on parttable1;
10、分区列 id2 新建为 clustered 索引并且不使用分区方案，parttable1 变成了非分区表
create clustered index CI_prattable1_id2 on parttable1(id2) ON [PRIMARY];
11、删掉上面 10 的 clustered 索引后，parttable1 还是非分区表
drop index CI_prattable1_id2 on parttable1;
12、分区列 id2 新建为 nonclustered 索引，虽然使用了分区方案，还是非分区表
create nonclustered index NCI_prattable1_id2 on parttable1(id2) on partschema1(Id2);
分区表转换为普通表，遇到分区字段是主键的情况下，则删除主键约束，再对原来主键的字段重建 cluster 索引或重建为主键，但是都不关联分区方案
ALTER TABLE Table_name DROP CONSTRAINT PK_NAME WITH (
CREATE CLUSTERED INDEX PK_NAME ON Table_name(column) WITH (ON [PRIMARY];
或
ALTER TABLE Table_name ADD CONSTRAINT PK_NAME PRIMARY KEY CLUSTERED(column) WITH (ON [PRIMARY];
普通表转换为分区表，要保留原来的主键的情况下，则删除主键约束，再创建主键但不设为聚集索引，再创建新的聚集索引，在该聚集索引中使用分区方案
ALTER TABLE Table_name DROP CONSTRAINT PK_NAME WITH (
ALTER TABLE Table_name ADD CONSTRAINT PK_NAME PRIMARY KEY NONCLUSTERED(column) WITH (ON [PRIMARY];
– 创建主键，但不设为聚集索引
CREATE CLUSTERED INDEX index_name ON Table_name(column) ON 分区方案(分区字段)
– 创建一个新的聚集索引，在该聚集索引中使用分区方案
查询某张分区表的总行数和大小，比如表为 crm.EmailLog
exec sp_spaceused crm.EmailLog
查询某张分区表的信息，每个分区有多少行，比如表为 crm.EmailLog
select convert(varchar(50), ps.name
) as partition_scheme,
p.partition_number,
convert(varchar(10), ds2.name
) as filegroup,
convert(varchar(19), isnull(v.value,), 120) as range_boundary,
str(p.rows, 9) as rows
from sys.indexes i
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id
join sys.partition_functions pf on ps.function_id = pf.function_id
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number – pf.boundary_value_on_right
WHERE i.object_id = object_id(crm.EmailLog)
and i.index_id in (0, 1)
order by p.partition_number
查询分区函数
select * from sys.partition_functions
查看分区架构
select * from sys.partition_schemes
感谢各位的阅读，以上就是“SqlServer 关于分区表的相关知识点有哪些”的内容了，经过本文的学习后，相信大家对 SqlServer 关于分区表的相关知识点有哪些这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！
