SQL server如何创建分区表

分区表的定义：

一般情况下，我们建立数据库表时，表数据都存放在一个文件里。但是如果是分区表的话，表数据就会按照你指定的规则分放到不同的文件里，把一个大的数据文件拆分为多个小文件，还可以把这些小文件放在不同的磁盘下由多个cpu进行处理。这样文件的大小随着拆分而减小，还得到硬件系统的加强，自然对我们操作数据是大大有利的。所以大数据量的数据表，对分区的需要还是必要的，因为它可以提高select效率，还可以对历史数据强行区分存档，对于多渠道导入数据时不容易锁表。

创建分区表的步骤：

1、创建数据库文件组；2、创建数据库文件；3、创建分区函数；4、创建分区方案；5、创建分区表

创建数据库文件组和文件，添加文件组，并为数据文件分配文件组–SQL脚本如下：

alter database jingzi add filegroup server2015
alter database jingzi add filegroup server2016
alter database jingzi add filegroup server2017
alter database jingzi add filegroup server2018
alter database jingzi add filegroup server2019
ALTER DATABASE jingzi
ADD FILE(NAME='jingzi_2015',FILENAME='C:\学生数据\jingzi\jingzi_2015.ndf',SIZE=3MB,MAXSIZE=UNLIMITED,FILEGROWTH=5MB)
TO FILEGROUP SERVER2015 
ALTER DATABASE [jingzi] 
ADD FILE(NAME='jingzi_2016',FILENAME='C:\学生数据\jingzi\jingzi_2016.ndf',SIZE=3MB,MAXSIZE=UNLIMITED,FILEGROWTH=5MB)
TO FILEGROUP SERVER2016
ALTER DATABASE [jingzi] 
ADD FILE(NAME='jingzi_2017',FILENAME='C:\学生数据\jingzi\jingzi_2017.ndf',SIZE=3MB,MAXSIZE=UNLIMITED,FILEGROWTH=5MB)
TO FILEGROUP SERVER2017
ALTER DATABASE [jingzi] 
ADD FILE(NAME='jingzi_2018',FILENAME='C:\学生数据\jingzi\jingzi_2018.ndf',SIZE=3MB,MAXSIZE=UNLIMITED,FILEGROWTH=5MB)
TO FILEGROUP SERVER2018
ALTER DATABASE [jingzi] 
ADD FILE(NAME='jingzi_2019',FILENAME='C:\学生数据\jingzi\jingzi_2019.ndf',SIZE=3MB,MAXSIZE=UNLIMITED,FILEGROWTH=5MB)
TO FILEGROUP SERVER2019

查看数据库文件组SQL语句：

select *  from sys.filegroups

创建分区函数Transact-SQL语法：

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ] 
FOR VALUES ( [ boundary_value [ ,...n ] ] )[ ; ]

// partition_function_name：分区函数的名称。input_parameter_type：用于分区的列的数据类型。　boundary_value：为使用 partition_function_name 的已分区表或索引的每个分区指定边界值。LEFT
| RIGHT 指定当间隔值由数据库引擎按升序从左到右排序时，boundary_value [ ,…n ]
属于每个边界值间隔的哪一侧（左侧还是右侧）。如果未指定，则默认值为 LEFT。
实例：建立分区函数

CREATE PARTITION FUNCTION PARTITION_FUNCTION_jingzi_date( DATETIME)
AS RANGE RIGHT
FOR VALUES( '2016-01-01','2017-01-01', '2018-01-01', '2019-01-01')

完成创建分区函数之后，可以通过以下SQL语句查看已创建的分区函数情况：

SELECT * FROM sys.partition_functions

创建分区方案
分区方案的作用是将分区函数生成的分区映射到文件组中去，分区方案是让SQL Server将已分区的数据放在哪个文件组中。
在当前数据库中创建一个将已分区表或已分区索引的分区映射到文件组的方案。 已分区表或已分区索引的分区的个数和域在分区函数中确定。必须首先在 CREATE PARTITION FUNCTION 语句中创建分区函数，然后才能创建分区方案。
创建分区方案的Transact-SQL语法：

CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name 
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )[ ; ]

tition_scheme_name：分区方案的名称；partition_function_name：使用分区方案的分区函数的名称；ALL：指定所有分区都映射到在 file_group_name 中提供的文件组，或映射到主文件组（如果指定了
[PRIMARY]。如果指定了 ALL，则只能指定一个
file_group_name；file_group_name | [ PRIMARY ] [ ,…n]：指定用来持有由 partition_function_name 指定的分区的文件组的名称。
file_group_name 必须已经存在于数据库中。
创建分区方案：

CREATE PARTITION SCHEME PARTITION_SCHEME_jingzi_date
AS PARTITION PARTITION_FUNCTION_jingzi_date
TO (SERVER2015, SERVER2016,SERVER2017, SERVER2018, SERVER2019 )

SQL语句查看已创建的分区方案：

SELECT * FROM sys.partition_schemes

创建分区表（test_table）

CREATE TABLE [test1_table]
(statdate DATETIME NOT NULL,
tableid int not null,
table_name varchar(20) null,)
ON PARTITION_SCHEME_jingzi_date (statdate )

table表创建之后，查看表的属性，可以看到test_table表已经是分区表。
验证分区表是否可行：
向测试表test_table插入数据：

INSERT INTO [dbo].[test1_table] (statdate,tableid ,table_name) 
VALUES ('2015-10-10','6','test6');
INSERT INTO [dbo].[test1_table] (statdate,tableid ,table_name) 
VALUES ('2016-12-31','2','test2');
INSERT INTO [dbo].[test1_table] (statdate,tableid ,table_name) 
VALUES ('2017-01-20','3','test3');
INSERT INTO [dbo].[test1_table] (statdate,tableid ,table_name) 
VALUES ('2018-10-20','4','test4');
INSERT INTO [dbo].[test1_table] (statdate,tableid ,table_name) 
VALUES ('2019-10-20','5','test5');

查看分区效果：

SELECT $PARTITION.PARTITION_FUNCTION_jingzi_date(statdate) 
AS 分区编号, COUNT(1) AS 记录数
FROM [test1_table]
GROUP BY $PARTITION.PARTITION_FUNCTION_jingzi_date(statdate)

上面可能有借用到各位老师的意见和数据库文件名称，出处已经找不着了，希望各位老师能原谅并指点指点！