SQLserver怎么创建分区表

1.分区表的定义：

通常，当我们构建数据库表时，表数据存储在一个文件中。但是，如果是分区表，则会按照您指定的规则将表数据分成不同的文件，将一个大数据文件拆分成多个小文件，并将这些小文件按多个文件放置在不同的磁盘下cpu正在处理。这样一来，文件大小随着拆分而减小，由硬件系统加强，对我们的运营数据自然大有裨益。因此，仍然需要对包含大量数据的数据表进行分区，因为这样可以提高select效率高，还可以强制区分历史数据归档，对于多渠道数据导入不容易锁表。

2.创建分区表的步骤：

• 1，创建数据库文件组。
• 2，创建数据库文件
• 3，创建分区函数
• 4，创建分区方案
• 5，创建分区表

3.示例：

让我们来看一个如何创建分区表的实际例子。 小公主，走吧！

3.1首先创建一个新数据库。mysqlDB，创建数据库文件组和文件，并添加文件组。

3.2 创建数据文件并将文件组分配给数据文件。

创建完成后的数据库文件信息

创建数据库文件组和文件，添加文件组，并将文件组分配给数据文件。--SQL脚本如下：

alter database mysqlDB add filegroup server2015
alter database mysqlDB add filegroup server2016
alter database mysqlDB add filegroup server2017
alter database mysqlDB add filegroup server2018
alter database mysqlDB add filegroup server2019

ALTER DATABASE [mysqlDB] ADD FILE(NAME=NmysqlDB_2015,FILENAME=ND:DATEBASEmysqlDB_2015.ndf,SIZE=3MB, MAXSIZE=UNLIMITED,FILEGROWTH=5MB)
TO FILEGROUP SERVER2015 --文件组
ALTER DATABASE [mysqlDB] ADD FILE(NAME=NmysqlDB_2016,FILENAME=ND:DATEBASEmysqlDB_2016.ndf,SIZE=3MB, MAXSIZE=UNLIMITED,FILEGROWTH=5MB)
TO FILEGROUP SERVER2016
ALTER DATABASE [mysqlDB] ADD FILE(NAME=NmysqlDB_2017,FILENAME=ND:DATEBASEmysqlDB_2017.ndf,SIZE=3MB, MAXSIZE=UNLIMITED,FILEGROWTH=5MB)
TO FILEGROUP SERVER2017
ALTER DATABASE [mysqlDB] ADD FILE(NAME=NmysqlDB_2018,FILENAME=ND:DATEBASEmysqlDB_2018.ndf,SIZE=3MB, MAXSIZE=UNLIMITED,FILEGROWTH=5MB)
TO FILEGROUP SERVER2018
ALTER DATABASE [mysqlDB] ADD FILE(NAME=NmysqlDB_2019,FILENAME=ND:DATEBASEmysqlDB_2019.ndf,SIZE=3MB, MAXSIZE=UNLIMITED,FILEGROWTH=5MB)
TO FILEGROUP SERVER2019

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

select *  from sys.filegroups

3.3 创建分区函数

创建分区函数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：用于分区的列的数据类型。 当用作分隔柱时， text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、别名数据类型或 CLR 除用户定义的数据类型外，所有数据类型均有效。
boundary_value：为使用 partition_function_name 分区表或索引的每个分区指定一个边界值。 如果 boundary_value 为空，则分区函数使 partition_function_name 将整个表或索引映射到单个分区。 只能使用 CREATE TABLE 或 CREATE INDEX 语句中指定的分区列。
LEFT | RIGHT 指定当间隔值由 数据库引擎 按升序从左到右排序时，boundary_value [ ,...n ] 每个边值区间的哪一侧(左侧或右侧)。 如果未指定，则默认为 LEFT。

示例演示：

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

SELECT * FROM sys.partition_functions

在完成创建分区函数之后，您可以使用以下内容。SQL查看创建的分区函数的语句：

SELECT * FROM sys.partition_functions

3.4 创建分区方案

分区方案的功能是将分区函数生成的分区映射到文件组。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 ] )[ ; ]

参数：
partition_scheme_name：分区方案的名称。 分区方案名称在数据库中必须是唯一的，并且符合标识符规则。
partition_function_name：使用分区方案的分区函数的名称。 分区函数创建的分区映射到分区方案中指定的文件组。 partition_function_name 数据库中必须已存在。 单个分区不能同时包含两个分区 FILESTREAM 和非 FILESTREAM 文件组。
ALL：指定将所有分区映射到 file_group_name 在主文件组中提供或映射到主文件组(如果已指定)的文件组。 [PRIMARY]。 如果已指定 ALL，则只能指定一个 file_group_name。
file_group_name | [ PRIMARY ] [ ,...n]：指定等待。 partition_function_name 指定分区的文件组的名称。 file_group_name 数据库中必须已存在。
如果已指定 [PRIMARY]，则分区存储在主文件组中。 如果已指定 ALL，则只能指定一个 file_group_name。 将分区分配给文件组的顺序来自分区。 1 开始，按中的文件组。 [,...n] 将分配中列出的订单。 在 [,...n] 在中，您可以多次指定相同的一个。 file_group_name。 如果 n 不够多，不能进去 partition_function_name 中指定的分区数，则 CREATE PARTITION SCHEME 将失败并返回错误。
如果 partition_function_name 如果生成的分区数少于文件组数，则将标记第一个未分配的文件组 NEXT USED并出现显示命名。 NEXT USED 文件组的信息。 如果已指定 ALL，然后是单独的 file_group_name 将为该 partition_function_name 保持它的 NEXT USED 属性。 如果在 ALTER PARTITION FUNCTION 在语句中创建分区，然后 NEXT USED 文件组将接收另一个分区。 要创建另一个具有新分区的未分配文件组，请使用 ALTER PARTITION SCHEME。
在 file_group_name[ 1,...n] 在中指定主文件组时，必须如中所示。 [PRIMARY] 分开，如中 PRIMARY因为它是一个关键词。

示例演示：

CREATE PARTITION SCHEME PARTITION_SCHEME_mysqlDB_date
AS PARTITION PARTITION_FUNCTION_mysqlDB_date
TO (SERVER2015, SERVER2016, SERVER2017, SERVER2018, SERVER2019 )

SELECT * FROM sys.partition_schemes

创建分区函数和分区方案后，可以在数据库的[存储]中查看：

通过能够通过以下测试SQL用于查看创建的分区方案的语句：

SELECT * FROM sys.partition_schemes

3.5 创建分区表(test_table）

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

完成test_table创建表后，请查看表的属性并查看test_table该表已经是已分区的表。

4.验证分区表是否可行：

向测试表test_table插入数据以查看分区效果。

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

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