SQL Server教程 - SQL SERVER 分区（PARTITION）

更新记录
转载请注明出处：https://www.cnblogs.com/cqpanda/p/16556437.html
2022年8月8日 发布。
2022年8月6日 从笔记迁移到博客。

SQL SERVER 分区（PARTITION）

分区说明

将数据放在不同的物理设备中

分区的好处

提高可维护性，降低维护难度

提高性能

分区的过程步骤

创建分区函数
创建分区方案
将分区方案应用到表上

可以在以下对象上分区：
无聚集索引的表（堆）
聚集索引
唯一索引
非聚集索引
注意：聚集索引分区时，需要指定的分区必须包含在聚集键中
如果要将分区的聚集或者非聚集索引不唯一，则分区列无需包含在键中
对于唯一索引（不论聚集非聚集）都必须包含分区列作为唯一索引键的一部分

创建分区函数

创建分区函数作用：指定根据表的某一列数据，将数据分配或映射到不同的分区中

注意：不能用作分区的字段数据类型是：
ext、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)
自定义的别名数据类型、公共语言运行库(CLR)用户定义数据类型

常用的分区函数使用的数据类型是：日期类型 和 整数类型
表或索引可以创建的最大分区数量为15000个

通常为了简化维护、改进性能，建议将每个分区放置在不同的文件组中
创建文件组时，务必使文件组的数据比分区函数中指定的边界值的数据多一个
文件组应当放置在不同的物理磁盘上，以分摊I/O开销

CREATE PARTITION FUNCTION [分区名] (分区数据类型)
AS RANGE [LEFT | RIGHT]
FOR VALUES('分割边界值','分割边界值','分割边界值',...);

创建分区方案

创建分区方案的作用：
将数据放在正确的文件组中
将分区函数创建的分区与文件组对应起来

创建分区方案需要设置的4个参数：
分区方案的名称
分区函数的名称，在分区方案创建之前必须先创建分区函数
ALL，使用该参数，文件组的数量为1
文件组列表

语法：

CREATE PARTITION SCHEME [分区方案名称]
AS PARTITION [分区函数名称]
TO(对应文件组1,对应文件组2,对应文件组3,对应文件组4);

将分区应用到表上-使用T-SQL

创建表时指定分区语法：

CREATE TABLE [表名]
(

) ON 分区方案名称(表中与分区对应的字段)

将分区应用到索引上-使用T-SQL

聚集索引分区：

CREATE CLUSTERED INDEX [索引名]
ON [表名](创建索引的字段名)
WITH(DROP_EXISTING = ON)
ON [分区方案名] (表中与分区方案对应的字段名);

创建表分区-使用SSMS

选中要分区的表或者索引

选择分区的列

选择新建分区函数 或 使用已经定义的分区函数

选择新建分区方案 或 使用已经建好的分区方案

确定分区方案的 范围 和 文件组

创建索引分区-使用SSMS

选择要进行分区的索引，进入分区页面

实例：为表创建分区方案

新建数据库

GO
-- 创建分区数据库
CREATE DATABASE [PandaDatabase2]
ON PRIMARY
(
    NAME = 'PandaDatabase2',
    FILENAME = 'C:\test2\PandaDatabase2.mdf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED,
)
LOG ON
(
    NAME = 'PandaDatabase2Log',
    FILENAME = 'C:\test2\PandaDatabase2Log.ldf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED,
);
GO

创建数据库文件组

GO
ALTER DATABASE [PandaDatabase2]
ADD FILEGROUP FileGroup2018;

ALTER DATABASE [PandaDatabase2]
ADD FILE
(
    NAME = 'PandaDatabase2_2018',
    FILENAME = 'C:\test2\PandaDatabase2_2018.ndf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED
) TO FILEGROUP [FileGroup2018];

ALTER DATABASE [PandaDatabase2]
ADD FILEGROUP FileGroup2019;

ALTER DATABASE [PandaDatabase2]
ADD FILE
(
    NAME = 'PandaDatabase2_2019',
    FILENAME = 'C:\test2\PandaDatabase2_2019.ndf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED
) TO FILEGROUP [FileGroup2019];

ALTER DATABASE [PandaDatabase2]
ADD FILEGROUP FileGroup2020;

ALTER DATABASE [PandaDatabase2]
ADD FILE
(
    NAME = 'PandaDatabase2_2020',
    FILENAME = 'C:\test2\PandaDatabase2_2020.ndf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED
) TO FILEGROUP [FileGroup2020];

ALTER DATABASE [PandaDatabase2]
ADD FILEGROUP FileGroup2021;

GO

创建分区函数

GO
-- 创建分区函数
CREATE PARTITION FUNCTION [PandaDatabaseFunction] (DATETIME)
AS RANGE LEFT
FOR VALUES('20181231','20191231','20201231');
GO

创建分区方案

GO
-- 创建分区方案
-- 注意：分区方案中的文件组必须多于分区函数中的个数
CREATE PARTITION SCHEME [PandaDatabaseSchema]
AS PARTITION [PandaDatabaseFunction]
TO(FileGroup2018,FileGroup2019,FileGroup2020,FileGroup2021);
GO

应用到指定表

GO
-- 应用到指定表
-- 注意不可以有主键约束
USE PandaDatabase2;
CREATE TABLE PandaTable4
(
    Id INT,
    Name CHAR(30) NOT NULL,
    join_datetime DATETIME
)
ON PandaDatabaseSchema(join_datetime);

测试插入数据

INSERT INTO [PandaTable4] VALUES(1,'Panda666','2020-3-30');
INSERT INTO [PandaTable4] VALUES(2,'Dog666','2019-3-30');
INSERT INTO [PandaTable4] VALUES(3,'Dog666','2018-3-30');
INSERT INTO [PandaTable4] VALUES(4,'Dog666','2018-2-10');