SQL SERVER 分区脚本备忘（二）

1、分区

分区可以分为 ：硬件分区、水平分区、垂直分区

对于大数据量的数据表，通过分区，可以提高select效率，还可以对历史数据进行归档。

2、新增文件组

ALTER DATABASE dbtest ADD FILEGROUP [FG_2015];
ALTER DATABASE dbtest ADD FILEGROUP [FG_2016];

ALTER DATABASE dbtest ADD FILEGROUP [FG_2017];

3、新增数据文件

一个文件组可以有多个数据文件，同一个文件组里的文件是均衡存储的。例如：一个文件组有3个文件，大小分别为1GB、2GB、3GB，如果累计往这个文件组里写入600MB的数据，则分别向3个文件写入100MB、200MB、300MB。

ALTER DATABASE dbtest ADD FILE(
    NAME = N'FG_2015_data',
    FILENAME = N'E:\Parctice\DBP\FG_2017_data.ndf' ,
    SIZE = 10MB ,
    FILEGROWTH = 100MB) TO FILEGROUP[FG_2015];

ALTER DATABASE dbtest ADD FILE(
    NAME = N'FG_2016_data',
    FILENAME = N'E:\Parctice\DBP\FG_2017_data.ndf' ,
    SIZE = 10MB ,
    FILEGROWTH = 100MB) TO FILEGROUP[FG_2016];

ALTER DATABASE dbtest ADD FILE(
    NAME = N'FG_2017_data',
    FILENAME = N'E:\Parctice\DBP\FG_2017_data.ndf' ,
    SIZE = 10MB ,
    FILEGROWTH = 100MB) TO FILEGROUP[FG_2017];

4、创建分区函数

目的是用来规范不同数据存放到不同目录的标准，即如何分区。

创建分区函数时，可以指定分区划分边界值的方式，即 range right  还是 range left ，符号“（）”表示不包含，“[]”表示包含。示例如下，“[”表示分区边界值所在位置 ：    

RANGE RIGHT    分区1)[分区2)

RANGE LEFT      分区1](分区2

CREATE PARTITION FUNCTION  Fun_FullDateAlternateKey(datetime) AS  RNAGE RIGHT 
FOR VALUES('2016/01/01')

5、创建分区方案，关联到分区函数

目的是将建好的分区函数组织成一套方案，简单点就是我们在那里对数据分区。

CREATE PARTITION SCHEME [Sch_FullDateAlternateKey] AS PARTITION [Fun_FullDateAlternateKey] TO ([FG_2015],[FG_2016])

6、查询分区信息

使用$patition函数。

用法：
为任何指定的分区函数返回分区号，一组分区列值将映射到该分区号中。

语法： 
[ database_name. ] $PARTITION.partition_function_name(expression)
 
参数：
database_name 
包含分区函数的数据库的名称。

partition_function_name 
对其应用一组分区列值的任何现有分区函数的名称。

expression 
其数据类型必须匹配或可隐式转换为其对应分区列数据类型的表达式。expression 也可以是当前参与 partition_function_name 的分区列的名称。

返回类型：
int 

备注：
$PARTITION 返回从 1 到分区函数的分区数之间的 int 值。

$PARTITION 将针对任何有效值返回分区号，无论此值当前是否存在于使用分区函数的分区表或索引中。

例如查询表的第一个分区，代码如下:

select * from dbtest.dbo.DimDate
where $partition.Fun_FullDateAlternateKey(FullDateAlternateKey)=1

--还可以按照分区进行分组来查看各个分区的数据行

select $partition.Fun_FullDateAlternateKey(FullDateAlternateKey)as Patition,
COUNT(*) countRow from dbo.DimDate 
group by $partition.Fun_FullDateAlternateKey(FullDateAlternateKey)

7、将分区的数据转移到另外一个表的分区

alter table dbtest.dbo.dimdate switch partition 2 to dbtest.dbo.dimdate_temp partition 2

8、添加分区

alter partition scheme [Sch_FullDateAlternateKey] next used [FG_2017]
alter partition function [Fun_FullDateAlternateKey]() split range('2017/01/01')

现在的分区区间如下：

文件组       分区        取值范围

FG_2015    1          [过去某年,2016/01/01)

FG_2016    2          [2016/01/01,2017/01/01)

FG_2017    3          [2017/01/01,未来某年)

9、删除分区

删除分区又称为合并分区，简单讲就是两个分区的数据进行合并。删除的时候指定边界值，删除的时候这个边界值属于哪个分区，就会删除这个分区，再向邻近的分区合并。领近的意思是以这个边界值为零界点的两个分区。例如如下，删除边界值为‘2016/01/01’的分区，‘2016/01/01’这个边界值属于文件组FG_2015，则将文件组FG_2015的数据合并到FG_2016之中去。

alter partition functin Fun_() merge range('2016/01/01')

现在的分区区间如下：

文件组       分区        取值范围

FG_2016    1          [过去某年,2017/01/01)

FG_2017    2          [2017/01/01,未来某年)

10、查看元数据

可通过以下三个系统视图，来查看我们的分区函数、分区方案、边界值点等。

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