背景：
一般情况下，我们建立数据库表时，表数据都存放在一个文件里。
但是如果是分区表的话，表数据就会按照你指定的规则分放到不同的文件里，把一个大的数据文件拆分为多个小文件，还可以把这些小文件放在不同的磁盘下由多个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
p.MsoNormal { margin: 0pt; margin-bottom: .0001pt; text-align: justify; font-family: Calibri; font-size: 10.5000pt }
h3 { margin-top: 5.0000pt; margin-bottom: 5.0000pt; text-align: left; font-family: 宋体; font-weight: bold; font-size: 13.5000pt }
h4 { margin-top: 5.0000pt; margin-bottom: 5.0000pt; text-align: left; font-family: 宋体; font-weight: bold; font-size: 12.0000pt }
h5 { margin-top: 5.0000pt; margin-bottom: 5.0000pt; text-align: left; font-family: 宋体; font-weight: bold; font-size: 10.0000pt }
span.10 { font-family: “Times New Roman” }
span.15 { font-family: “Courier New”; font-size: 10.0000pt }
p.p { margin-top: 5.0000pt; margin-right: 0.0000pt; margin-bottom: 5.0000pt; margin-left: 0.0000pt; text-align: left; font-family: Calibri; font-size: 12.0000pt }
p.pre { margin: 0pt; margin-bottom: .0001pt; text-align: left; font-family: 宋体; font-size: 12.0000pt }
span.msoIns { text-decoration: underline; color: blue }
span.msoDel { text-decoration: line-through; color: red }
table.MsoNormalTable { font-family: “Times New Roman”; font-size: 10.0000pt }
div.Section0 { }
