sql server 表分区
背景：
一般情况下，我们建立数据库表时，表数据都存放在一个文件里。
但是如果是分区表的话，表数据就会按照你指定的规则分放到不同的文件里，把一个大的数据文件拆分为多个小文件，还可以把这些小文件放在不同的磁盘下由多个cpu进行处理。这样文件的大小随着拆分而减小，还得到硬件系统的加强，自然对我们操作数据是大大有利的。
1.创建文件组
alter database <数据库名> add filegroup <文件组名>
alter database HARVEY add filegroup ByIdGroup1
alter database HARVEY add filegroup ByIdGroup2
alter database HARVEY add filegroup ByIdGroup3
2.创建数据文件到文件组里面
alter database <数据库名称> add file <数据标识> to filegroup <文件组名称>
alter database HARVEY add file
(name=N'ById1',filename=N'C:\Users\Administrator\Desktop\DATA\file1.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup1
alter database HARVEY add file
(name=N'ById2',filename=N'C:\Users\Administrator\Desktop\DATA\file2.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup2
alter database HARVEY add file
(name=N'ById3',filename=N'C:\Users\Administrator\Desktop\DATA\file3.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup3
若以后想增加文件到文件组，只需
alter database HARVEY add file
(name=N'ById4',filename=N'C:\Users\Administrator\Desktop\DATA\file4.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup1
还有另外的文件方式如下等，不再扩展
file(
NAME = N'File1',
FILENAME = N'G:\data\FG1\File1.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 10%
)
注意：加上 N 代表存入数据库时以 Unicode 格式存储。
N'string' 表示string是个Unicode字符串
3.创建分区函数
--创建分区函数语法
create partition function 分区函数名(<分区列类型>) as range [left/right] for values (每个分区的边界值,....)
--删除分区语法
drop partition function <分区函数名>
--创建分区函数
CREATE PARTITION FUNCTION[bgPartitionFun](int) AS RANGE LEFT FOR VALUES (
N'2',--sql server自动将字符串转为int类型
N'4',
N'6') --4个分区
--按照时间分区函数
DECLARE @dt datetime
SET @dt = '20020101'
CREATE PARTITION FUNCTION PF_HistoryArchive(datetime)
AS RANGE LEFT|RIGHT
FOR VALUES(
@dt, --string类型，sql server会自动将字符串转化为datetime
DATEADD(Year, 1, @dt))
注意：LEFT相当于左区间，RIGHT相当于右区间
4.创建分区方案
--创建分区方案语法
create partition scheme <分区方案名称> as partition <分区函数名称> [all]to (文件组名称,....)
--删除分区方案语法
drop partition scheme<分区方案名称>
--删除分区方案 bgPartitionSchema
drop partition scheme bgPartitionSchema1
CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO ([ByIdGroup1], [ByIdGroup2], [ByIdGroup3],[ByIdGroup1])
注意：分区函数必须关联分区方案才能有效，然而分区方案指定的文件组数量必须与分区数量一致，哪怕多个分区存放在一个文件组中。
5.分区索引
--创建分区索引语法
create <索引分类> index <索引名称> on <表名>(列名)on <分区方案名>(分区依据列名)
--创建分区索引
CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema] ON [dbo].[BigOrder]
(
[OrderId]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([OrderId])
注意：使用分区索引查询，可以避免多个cpu操作多个磁盘时产生的冲突。
6.重建索引（对已存在的表进行分区）
（删除聚集索引以及需要分区字段的索引后重建该类索引，表被按分区值将分配到各文件组）
EXEC sp_helpindex N'BigOrder' --查看orders中使用的索引,删除clustered索引
drop index idx_cl_od on BigOrder;
go
CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema] ON [dbo].[BigOrder]
{
[OrderId]
}
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([OrderId])
go
7.建立分区表
--创建分区表语法
create table <表名> (
<列定义>
)on<分区方案名>(分区列名)
--创建分区表create table BigOrder (
OrderId int identity,
orderNum varchar(30) not null,
CreateDate datetime null default getdate()
)on bgPartitionSchema(OrderId)
8.判断数据在哪个分区上
select $partition.<分区函数>(分区列名)
select $partition.bgPartitionFun(3)
select $partition.bgPartitionFun(b.orderid) as partitionNum,b.* from BigOrder b
其他
查看分区信息sql
SELECT OBJECT_NAME(p.object_id) AS ObjectName,
i.name AS IndexName,
p.index_id AS IndexID,
ds.name AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
prv_left.value AS LowerBoundaryValue,
prv_right.value AS UpperBoundaryValue,
CASE pf.boundary_value_on_right
WHEN 1 THEN 'RIGHT'
ELSE 'LEFT' END AS Range,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i
ON i.object_id = p.object_id
AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds
ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS ps
ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions AS pf
ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds2
ON dds2.partition_scheme_id = ps.data_space_id
AND dds2.destination_id = p.partition_number
JOIN sys.filegroups AS fg
ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
ON ps.function_id = prv_left.function_id
AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right
ON ps.function_id = prv_right.function_id
AND prv_right.boundary_id = p.partition_number
WHERE
OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
UNION ALL
SELECT
OBJECT_NAME(p.object_id) AS ObjectName,
i.name AS IndexName,
p.index_id AS IndexID,
NULL AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
NULL AS LowerBoundaryValue,
NULL AS UpperBoundaryValue,
NULL AS Boundary,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i
ON i.object_id = p.object_id
AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds
ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups AS fg
ON fg.data_space_id = i.data_space_id
WHERE
OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY
ObjectName,
IndexID,
PartitionNumber
查看分区的相关情况
--查看分区及分区范围的情况
select * from sys.partitions where object_id = object_id('orders');
select * from sys.partition_range_values;
--查看分区架构情况
select * from sys.partition_schemes;
--查看某一特定分区列值属于哪个分区
select Performance.$partition.Part_func_orders('20050325') as partition_num;
--查看某一特定分区的记录
select * from orders where Performance.$partition.Part_func_orders(orderdate) = 2
