关于SqlServer2008R2分区管理的讲解

标签（空格分隔）： .Net

SqlServer2008R2 分区管理 分区的背景 分区的优劣 分区操作 创建文件与文件组 创建分区函数与分区方案 映射数据 查看分区 参看每个分区的总数据 查看某个分区的流水 查看一笔数据属于哪个分区 删除分区

分区的背景

当数据量太大时，可以通过数据分区来提升效率

分区的优劣

优势

提供性能：这个是大多人数分区的目的，把一个表分部到不同的硬盘或其他存储介质中，提高并发，会大大提升查询速度。 提高稳定性：当一个分区出了问题，不会影响其他分区，仅仅是当前坏的分区不可用。 便于管理：把一个大表分成若干个小表，则备份和恢复的时候不再需要备份整个表，可以单独备份分区。 存档：将一些不太常用的数据，单独存放。如：将1年前的数据记录分到一个专门的存档服务器存放。

劣势

跨分区的查询数据处理不好速度会减慢

分区操作

创建文件与文件组

一个文件只可以对应一个文件组，一个文件组可以对应多个文件，最终数据是相对于文件组操作，这样数据操作更简单，而一个文件组中包含多个文件的话，数据插入是按初始比例插入

查询效率：单一文件组多分区文件 > 多文件组多分区文件 >单一文件组单一分区文件 > 默认

语法：alter database <数据库名> add filegroup <文件组名>
alter database FenQuTest add filegroup Group1
alter database FenQuTest add filegroup Group2
alter database FenQuTest add filegroup Group3

语法：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 FenQuTest add file (name=N'g1',filename=N'C:\fq\g1.ndf',size=5Mb,filegrowth=5mb) to filegroup Group1
alter database FenQuTest add file (name=N'g2',filename=N'C:\fq\g2.ndf',size=5Mb,filegrowth=5mb) to filegroup Group2 
alter database FenQuTest add file (name=N'g3',filename=N'C:\fq\g3.ndf',size=5Mb,filegrowth=5mb) to filegroup Group3

创建分区函数与分区方案

分区函数： 以时间（datetime）分割来创建分区方案，等于分割数据的数据归右边分区（RANGE RIGHT）

CREATE PARTITION FUNCTION [OrderInfo_SubareaFun](datetime) AS RANGE RIGHT FOR VALUES (
N'2017-07-01T00:00:00',N'2017-08-01T00:00:00', N'2017-09-01T00:00:00')

分区方案

 CREATE PARTITION SCHEME [OrderInfo_SubareaSCHEME] AS PARTITION [OrderInfo_SubareaFun] TO ([PRIMARY],[Group1],[Group2],[Group3])   

映射数据

映射数据：确保表中不含聚集索引

–删除表中的聚集索引

ALTER TABLE [dbo].[Test] DROP CONSTRAINT [PK__Test_Id]

ALTER TABLE [dbo].[Test] ADD CONSTRAINT [PK__Test_Id] PRIMARY KEY NONCLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

--映射数据
CREATE CLUSTERED INDEX [ClusteredIndex_on_Test_SubareaPlan] ON [dbo].[Test] 
(
    [CreateOn]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [OrderInfo_SubareaSCHEME]([CreateOn])

查看分区

参看每个分区的总数据

SELECT $PARTITION.[OrderInfo_SubareaFun](CreateOn) AS Partition, 
COUNT(*) AS [COUNT]    
FROM dbo.Test    
GROUP BY $PARTITION.[OrderInfo_SubareaFun](CreateOn)    
ORDER BY Partition ;

查看某个分区的流水

SELECT * 
FROM dbo.Test 
WHERE $PARTITION.[OrderInfo_SubareaFun](CreateOn) =3

查看一笔数据属于哪个分区

 select $partition.[OrderInfo_SubareaFun]('2017-9-1') 

删除分区

alter partition function OrderInfo_SubareaFun()

merge range(N’1500000’)