SQL Server 数据库分区分表（水平分表）详细步骤-木庄网络博客

本文整理自网络，侵删。

1、需求说明

将数据库Demo中的表按照日期字段进行水平分区分表。要求数据文件按一年一个文件存储，且分区的分割点会根据时间的增长自动添加（例如现在是2017年1月1日，将其作为一个分割点，即将2017年1月1日之前的数据存储到数据文件A中，将2017年1月1日的之后的数据存储到数据文件B中；当时间到2018年1月1日时，自动将2018年1月1日添加为一个新的分区分割点，并将2017年1月1日至2018年1月1日的数据存储在数据文件B中，将2018年1月1日之后的数据存储在一个新的数据文件C中，以此类推）。

2、实现思路

2.1分区原理

要实现这一功能，首先要了解数据库对水平分区表进行分区存储的原理。

所谓水平分区分表，就是把逻辑上的一个表，在物理上按照你指定的规则分放到不同的文件里，把一个大的数据文件拆分为多个小文件，还可以把这些小文件放在不同的磁盘下。这样把一个大的文件拆分成多个小文件，便于我们对数据的管理。

2.2 水平分区优点

l 便于存档

l 便于管理：备份恢复时可以单一的备份或者恢复某一个分区

l 提高可用性：一个分区故障，不影响其他分区的正常使用

l 提高性能：提升查询数据的速度

2.3 实现思路

① 创建数据库

② 在创建的数据库中添加文件组

③ 在文件组中添加新的文件

④ 定义分区函数

⑤ 定义分区架构

⑥ 定义分区表

⑦ 定义代理作业，自动添加分区分割点

⑧ 测试数据

注意：

² 分区表依赖于分区架构，而分区架构又依赖与分区函数，所以在穿件分区函数、分区架构、分区表是要按照对应的顺序创建。

² 分区函数并不属于具体的分区架构和分区表，它们之间仅仅是使用关系。

² 分区表只能在创建的时候指定为分区表

3、实现步骤

3.1代码创建分区表

3.1.1 创建数据库

新建数据库，并将其命名为Demo

3.1.2 添加文件组

代码格式：

ALTER DATABASE <数据库名称> ADD FILEGROUP<文件组名>

代码示例：

ALTER DATABASE DemoADD FILEGROUP DemoFileGroup

3.1.3 添加文件

代码格式：

ALTER DATABASE <数据库名称> ADD FILE <数据标识> TO FILEGROUP<文件组名称>

注意：数据标识中name为逻辑文件名、filename为物理文件路径名、size为文件初始大小（单位：kb/mb/gb/tb）、filegrowth为文件自动增量（单位：kb/mb/gb/tb）、maxsize为文件增大的最大大小（单位：kb/mb/gb/tb/unlimited）

代码示例：

ALTER DATABASEDemo ADD FILE (
 NAME='DemoFile1',
FILENAME='D:\ProgramFiles\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DemoFile1.mdf',
 SIZE=5MB,
 FILEGROWTH=5MB)
 TOFILEGROUP DemoFileGroup

在此我们重复执行示例代码，在示例文件组下添加三个文件。

3.1.4 定义分区函数

分区函数是用于判定数据行该属于哪个分区,通过分区函数中设置边界值来使得根据行中特定列的值来确定其分区。

代码格式：

CREATE PARTITIONFUNCTION partition_function_name( input_parameter_type )

   ASRANGE [ LEFT | RIGHT ]

   FORVALUES ( [ boundary_value [ ,...n ] ] )

   [ ; ]

其中“LEFT”和“RIGHT”决定了“VALUES”中的边界值被划分到哪一个分区中（即，边界值属于左侧分区还是右侧分区）。

代码示例：

CREATE PARTITIONFUNCTION DemoPartitionFunction( datetime2(0) )
 ASRANGE RIGHT
 FORVALUES ('2016-01-01 00:00:00','2017-01-01 00:00:00')

查看分区函数是否创建成功：

SELECT * FROM sys.partition_functions

3.1.5 定义分区架构

定义完分区函数仅仅是知道了如何将列的值区分到了不同的分区，而每个分区的存储方式，则需要分区构架来定义。分区构架仅仅是依赖分区函数.分区构架中负责分配每个区属于哪个文件组，而分区函数是决定如何在逻辑上分区。

代码格式：

CREATE PARTITIONSCHEME partition_scheme_name

   ASPARTITION partition_function_name

   [ ALL ]TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )

   [ ; ]

代码示例：

CREATE PARTITIONSCHEME DemoPartitionScheme
 ASPARTITION DemoPartitionFunction
 TO ( DemoFileGroup,[PRIMARY],DemoFileGroup)

查看分区架构是否创建完成：

SELECT * FROM sys.partition_schemes

3.1.6 定义分区表

表在创建的时候就已经决定是否是分区表了。虽然在很多情况下都是你在发现已经表已经足够大的时候才想到要把表分区，但是分区表只能够在创建的时候指定为分区表。

代码格式：

CREATE TABLEtable_name(

...

) ONpartition_scheme_name(column_name)

代码示例：

CREATE TABLEDemoTable(
demo_id BIGINT,
demo_date datetime2(0),
demo_desc varchar(50)
) ONDemoPartitionScheme(demo_date)

3.2 界面向导表分区

在3.4、3.5、3.6中，我们已经详细的描述了如何定义分区函数、分区架构以及分区表，但这些都是通过代码实现的，下面，我们来通过SQL Server 2012 Management Studio的界面向导创建分区表。

3.2.1 创建数据库

右键点击“数据库”，选择第一项“新建数据库(N)…”，新建数据库，如图1所示：

图1 新建数据库

3.2.2 创建文件组

右键数据库Demo，选择“属性”，如图2所示：

图2 数据库属性界面

在属性界面中，点击箭头所示的“文件组”选项，进入文件组编辑界面，如图3所示：

图3 文件组管理界面

在文件组管理界面中点击箭头①所示的“添加”选项，添加新的文件组，界面中会出现箭头②所示的属性框，并键入对应的属性值，之后点击“确定”，完成新建文件组。

3.2.3 添加文件

和添加文件组的方式一样，右键数据库Demo，选择“属性”，打开数据库属性界面，这次选择“文件”，打开文件管理界面，如图4所示：

阅读剩余部分

数据库系统概念第6版

￥41.1元 机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » SQL Server 数据库分区分表（水平分表）详细步骤

SQL Server 数据库分区分表（水平分表）详细步骤

目录

1、需求说明

2、实现思路

2.1分区原理

2.2 水平分区优点

2.3 实现思路

3、实现步骤

3.1代码创建分区表

3.1.1 创建数据库

3.1.2 添加文件组

3.1.3 添加文件

3.1.4 定义分区函数

3.1.5 定义分区架构

3.1.6 定义分区表

3.2 界面向导表分区

3.2.1 创建数据库

3.2.2 创建文件组

3.2.3 添加文件

数据库系统概念第6版

相关推荐

评论

目录

1、 需求说明

2、实现思路

2.1分区原理

2.2 水平分区优点

2.3 实现思路

3、实现步骤

3.1代码创建分区表

3.1.1 创建数据库

3.1.2 添加文件组

3.1.3 添加文件

3.1.4 定义分区函数

3.1.5 定义分区架构

3.1.6 定义分区表

3.2 界面向导表分区

3.2.1 创建数据库

3.2.2 创建文件组

3.2.3 添加文件

相关推荐

评论

1、需求说明