MSSQLSERVER 分区创建 分区合并删除 分区

1.当业务生产数据量大的时候，我们需要将一个大表拆分为多个较小的表，我们要的查询更快，基本原理就是，因为要扫描的数据变的更小。维护任务重新生成索引或备份表更快的运行。

2.我们可以再不通过将表物理放置在多个磁盘驱动器上来拆分表的情况下获取分区。如果将某个表放置在一个物理驱动器上，将相关表放置在另一个驱动器上，则可以提高查询性能，因为当运行涉及表间连接的查询时，多个磁盘头同时读取数据。可以使用SQL Server文件组来指定放置表的磁盘，在多cpu及多硬盘的服务器上，有效的利用了i/o并行处理，使得不至于存取数据时卡死在某一个物理硬盘上；

3.分区的方式，基本就三种方式：硬件分区、水平分区、垂直分区；

缺点：

  一般来说分区表是用的不同的文件组 从外面往不同的文件组插入数据是有可能比普通表慢得

不说了下面基于原始表创建的分区 

1.分区步骤

   a.创建分区函数

   b.创建分区架构

   c.对表进行分区表

基于缓存更新机制，我使用时间来进行分区，这里大家根据业务的要求使用合适的字段来作为分区：

我这里用200万这个表[bpmExtend]来操作下，

首先添加文件组：

ALTER DATABASE [bpmExtend] ADD FILEGROUP OA2013

GO

ALTER DATABASE [bpmExtend] ADD FILEGROUP OA2014

GO

ALTER DATABASE [bpmExtend] ADD FILEGROUP OA2015

GO

ALTER DATABASE [bpmExtend] ADD FILEGROUP OA2016

GO

添加文件并把其指向指定文件组

ALTER DATABASE [bpmExtend] 
ADD FILE
(
NAME='OA2013_T',
FILENAME ='D:\sqldata\OA2013_T.NDF'
)
TO FILEGROUP OA2013
GO
------------------------------------------------------------------------
ALTER DATABASE [bpmExtend] 
ADD FILE
(
NAME='OA2014_T',
FILENAME ='D:\sqldata\OA2014_T.NDF'
)
TO FILEGROUP OA2014
GO
-----------------------------------------------------------------------
ALTER DATABASE [bpmExtend] 
ADD FILE
(
NAME='OA2015_T',
FILENAME ='D:\sqldata\OA2015_T.NDF'
)
TO FILEGROUP OA2015
GO
-----------------------------------------------------------------------------
ALTER DATABASE [bpmExtend] 
ADD FILE
(
NAME='OA2016_T',
FILENAME ='D:\sqldata\OA2016_T.NDF'
)
TO FILEGROUP OA2016
GO

以后删除分区，会报不为空无法删除的error

下面为解决error的脚本

-----------删除不为空的的文件--
ALTER DATABASE [bpmExtend] 
MODIFY FILE
(
NAME = OA2014,
SIZE = 2MB
);

ALTER  DATABASE [bpmExtend] REMOVE FILE OA2014

创建分区函数，下面一系列都是根据分区函数来的

CREATE PARTITION FUNCTION FUN_OA (DATETIME) AS RANGE RIGHT FOR VALUES 
(
'20140101 23:59:59.997',
'20150101 23:59:59.997',
'20160101 23:59:59.997'
)
--删除分区函数
--DROP  PARTITION FUNCTION FUN_OA

这里用的right函数，意思是'20140101 23:59:59.997'之前的数据归于第一分区，也可以这样理解把'20140101 23:59:59.997 定义为第一分区；

下面就创建分区方案

CREATE PARTITION SCHEME SCH_OA
AS PARTITION FUN_OA TO
(
OA2013,
OA2014,
OA2015,
OA2016
)

---如何将一个普通表转换成一个分区表?说到底，只要将该表创建一个聚集索引，并在聚集索引上使用分区方案

删除原表的聚集索引

ALTER TABLE [dbo].[Opinion_t] DROP CONSTRAINT PK_CALL;

-------------------------------------------------------------

ALTER TABLE [dbo].[Opinion_t]  ADD CONSTRAINT PK_CALLS PRIMARY KEY NONCLUSTERED (ID ASC )

-------------------------------------------------------------------------------

创建分区字段的聚集索引
CREATE CLUSTERED INDEX IX_POSTTIME ON [dbo].[Opinion_t] ([PostTime]) ON SCH_OA ([PostTime])

--------查看第几个分区-------------------------

SELECT $PARTITION.FUN_OA ('20140101 ') 当时定义的第一个分区，执行如下图：

下面统计分区表的行数：

SELECT $PARTITION.FUN_OA(POSTTIME),COUNT(ID) FROM [dbo].[Opinion_t]
GROUP BY $PARTITION.FUN_OA(POSTTIME)

为了公平起见也在原始表上创建一个非聚集索引来和分区表做下性能对比，

CREATE INDEX IX_POSTTIME ON [dbo].[Opinion] ([PostTime])

接下来执行一个简单的查询：

DBCC DROPCLEANBUFFERS--清下缓存--生产库不要随意清
SET STATISTICS IO ON 
SET STATISTICS TIME ON 


SELECT *
FROM [dbo].[Opinion]  WHERE [PostTime]>'2015-12-25 16:03:54.000'


SELECT*
 FROM [dbo].[Opinion_t] WHERE [PostTime]>'2015-12-25 16:03:54.000'

下面是执行计划（预读是因清了缓存）：

原始表 'Opinion'。扫描计数 1，逻辑读取 48089 次，物理读取 3 次，预读 48092 次，lob 逻辑读取 979319 次，lob 物理读取 1060 次，lob 预读 0 次。

 CPU 时间 = 1108 毫秒，占用时间 = 20178 毫秒。

分区表：

表 'Opinion_t'。扫描计数 2，逻辑读取 15508 次，物理读取 5 次，预读 15497 次，lob 逻辑读取 979346 次，lob 物理读取 689 次，lob 预读 0 次。

  CPU 时间 = 1201 毫秒，占用时间 = 9507 毫秒。

查看分区所有信息

select * from sys.partition_functions   

select * from sys.partition_range_values
select * from sys.partition_schemes