技术分享 | SQL Server表分区介绍
点击上方SQL学习之路，关注获取SQL视频教程
作者：张龙豪
来源：www.cnblogs.com/knowledgesea
一般情况下，我们建立数据库表时，表数据都存放在一个文件里。
但是如果是分区表的话，表数据就会按照你指定的规则分放到不同的文件里，把一个大的数据文件拆分为多个小文件，还可以把这些小文件放在不同的磁盘下由多个cpu进行处理。这样文件的大小随着拆分而减小，还得到硬件系统的加强，自然对我们操作数据是大大有利的。
所以大数据量的数据表，对分区的需要还是必要的，因为它可以提高select效率，还可以对历史数据经行区分存档等。但是数据量少的数据就不要凑这个热闹啦，因为表分区会对数据库产生不必要的开销，除啦性能还会增加实现对象的管理费用和复杂性。
先跟着做一个分区表（分为11个分区），去除神秘的面纱，然后咱们再逐一击破各个要点要害。
分区是要把一个表数据拆分为若干子集合，也就是把把一个数据文件拆分到多个数据文件中，然而这些文件的存放可以依托一个文件组或这多个文件组，由于多个文件组可以提高数据库的访问并发量，还可以把不同的分区配置到不同的磁盘中提高效率，所以创建时建议分区跟文件组个数相同。
可以点击数据库属性在文件组里面添加
T-sql语法：
alter database <数据库名> add filegroup <文件组名>
---创建数据库文件组
alter database testSplit add filegroup ByIdGroup1
alter database testSplit add filegroup ByIdGroup2
alter database testSplit add filegroup ByIdGroup3
alter database testSplit add filegroup ByIdGroup4
alter database testSplit add filegroup ByIdGroup5
alter database testSplit add filegroup ByIdGroup6
alter database testSplit add filegroup ByIdGroup7
alter database testSplit add filegroup ByIdGroup8
alter database testSplit add filegroup ByIdGroup9
alter database testSplit add filegroup ByIdGroup10
可以点击数据库属性在文件里面添加
T-sql语法：
alter database <数据库名称>
add file <数据标识>
to filegroup <文件组名称>
--<数据标识>
--name:文件名，fliename:物理路径文件名，
--size:文件初始大小kb/mb/gb/tb，
--filegrowth:文件自动增量kb/mb/gb/tb/%,
--maxsize:文件可以增加到的最大大小kb/mb/gb/tb/unlimited）
alter database testSplit add file
(name=N'ById1',filename=N'J:\Work\数据库\data\ById1.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup1
alter database testSplit add file
(name=N'ById2',filename=N'J:\Work\数据库\data\ById2.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup2
alter database testSplit add file
(name=N'ById3',filename=N'J:\Work\数据库\data\ById3.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup3
alter database testSplit add file
(name=N'ById4',filename=N'J:\Work\数据库\data\ById4.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup4
alter database testSplit add file
(name=N'ById5',filename=N'J:\Work\数据库\data\ById5.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup5
alter database testSplit add file
(name=N'ById6',filename=N'J:\Work\数据库\data\ById6.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup6
alter database testSplit add file
(name=N'ById7',filename=N'J:\Work\数据库\data\ById7.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup7
alter database testSplit add file
(name=N'ById8',filename=N'J:\Work\数据库\data\ById8.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup8
alter database testSplit add file
(name=N'ById9',filename=N'J:\Work\数据库\data\ById9.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup9
alter database testSplit add file
(name=N'ById10',filename=N'J:\Work\数据库\data\ById10.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup10执行完成后，右键数据库看文件组跟文件里面是不是多出来了这些文件组跟文件。
右键到要分区的表---> 存储 ---> 创建分区 --->显示向导视图 ---> 下一步 ---> 下一步。。
这里举例说下选择列的意思：
假如你选择的是int类型的列：那么你的分区可以指定为1--100W是一个分区，100W--200是另外一个分区....
假如你选择的是datatime类型：那么你的分区可以指定为：2014-01-01--2014-01-31一个分区，2014-02-01--2014-02-28是另外一个分区...
根据这样的列数据规则划分，那么在那个区间的数据，在插入数据库时就被指向那个分区存储下来。
我这里选用orderid int类型 ---> 下一步 --->
左边界右边界：就是把临界值划分给上一个分区还是下一个分区。一个小于号，一个小于等于号。
然后下一步下一步最后你会得到分区函数和分区方案。
USE [testSplit]
GO
BEGIN TRANSACTION
--创建分区函数
CREATE PARTITION FUNCTION [bgPartitionFun](int)
AS RANGE LEFT FOR VALUES
(N'1000000', N'2000000', N'3000000', N'4000000',
N'5000000', N'6000000', N'7000000', N'8000000',
N'9000000', N'10000000')
--创建分区方案
CREATE PARTITION SCHEME [bgPartitionSchema]
AS PARTITION [bgPartitionFun] TO
([PRIMARY], [ByIdGroup1], [ByIdGroup2], [ByIdGroup3],
[ByIdGroup4], [ByIdGroup5], [ByIdGroup6], [ByIdGroup7],
[ByIdGroup8], [ByIdGroup9], [ByIdGroup10])
--创建分区索引
CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165]
ON [dbo].[BigOrder]
(
[OrderId]
)WITH (SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF)
ON [bgPartitionSchema]([OrderId])
--删除分区索引
DROP INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165]
ON [dbo].[BigOrder]
WITH ( ONLINE = OFF )
COMMIT TRANSACTION执行上面向导生成的语句。分区完成。。
首先我在表中插入啦1千万行数据。给表分啦11个分区。前十个分区里面一个是100W条数据。。
说两句：
可见反常现象，扫描次数跟逻辑读取次数都是无分区表的2倍之多，但查询速度却是快啦不少啊。这就是分区的神奇之处啊，所以要相信这世界一切皆有可能。
指定分依据区列（依据列唯一），分区数据范围规则，分区数量，然后将数据映射到一组分区上。
创建语法：
create partition function 分区函数名(<分区列类型>)
as range [left/right]
for values (每个分区的边界值,....)--创建分区函数
CREATE PARTITION FUNCTION [bgPartitionFun](int)
AS
RANGE LEFT FOR VALUES
(N'1000000', N'2000000', N'3000000', N'4000000', N'5000000',
N'6000000', N'7000000', N'8000000', N'9000000', N'10000000')然而，分区函数只定义了分区的方法，此方法具体用在哪个表的那一列上，则需要在创建表或索引是指定。
删除语法：
--删除分区语法
drop partition function <分区函数名>--删除分区函数 bgPartitionFun
drop partition function bgPartitionFun需要注意的是，只有没有应用到分区方案中的分区函数才能被删除。
指定分区对应的文件组。
创建语法：
--创建分区方案语法
create partition scheme <分区方案名称>
as partition <分区函数名称> [all] to (文件组名称,....)--创建分区方案,所有分区在一个组里面
CREATE PARTITION SCHEME [bgPartitionSchema]
AS
PARTITION [bgPartitionFun] TO
([ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1],
[ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1],
[ByIdGroup1], [ByIdGroup1], [ByIdGroup1])分区函数必须关联分区方案才能有效，然而分区方案指定的文件组数量必须与分区数量一致，哪怕多个分区存放在一个文件组中。
删除语法：
--删除分区方案语法
drop partition scheme<分区方案名称>--删除分区方案 bgPartitionSchema
drop partition scheme bgPartitionSchema1只有没有分区表，或索引使用该分区方案是，才能对其删除。
创建语法：
--创建分区表语法
create table <表名> (
<列定义>
)on<分区方案名>(分区列名)--创建分区表
create table BigOrder (
OrderId int identity,
orderNum varchar(30) not null,
OrderStatus int not null default 0,
OrderPayStatus int not null default 0,
UserId varchar(40) not null,
CreateDate datetime null default getdate(),
Mark nvarchar(300) null
)on bgPartitionSchema(OrderId)如果在表中创建主键或唯一索引，则分区依据列必须为该列。
创建语法：
--创建分区索引语法
create <索引分类> index <索引名称>
on <表名>(列名)
on <分区方案名>(分区依据列名)--创建分区索引
CREATE CLUSTERED INDEX
[ClusteredIndex_on_bgPartitionSchema_635342971076448165]
ON [dbo].[BigOrder]
(
[OrderId]
)WITH (SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF)
ON [bgPartitionSchema]([OrderId])使用分区索引查询，可以避免多个cpu操作多个磁盘时产生的冲突。
扩展阅读
公众号内回复‘M’获取文章目录，搜索QQ群“SQL学习之路”加群讨论
长按识别二维码，
更多精彩早知道！
原文作者：；如有侵权，请联系管理员删除。
