SQL SERVER分区函数Word文件下载.docx
《SQL SERVER分区函数Word文件下载.docx》由会员分享，可在线阅读，更多相关《SQL SERVER分区函数Word文件下载.docx（25页珍藏版）》请在冰豆网上搜索。
一般而言，衡量大型表是以数据为标准的，但对于适合分区的大型表，衡量大型表更重要的是对数据访问的性能，如果对于某些表的访问和维护有较严重的性能问题，就可以视为大型表，就应该考虑通过更好的设计和分区来解决性能问题。
创建分区表必须经过如下三个步骤：
1，创建分区函数
2，创建映射到分区函数的分区方案
3，创建使用该分区方案的分区表
分区函数
分区函数是数据库中的一个独立对象，它将表的行映射到一组分区，所以分区函数解决的是HOW的问题，即表如何分区的问题。
创建分区函数时，必须指明数据分区的边界点以及分区依据列，这样便知道如何对表或索引进行分区。
分区函数的创建语法如下：
CREATEPARTITIONFUNCTIONpartition_function_name（input_parameter_type）
ASRANGE[LEFT|RIGHT]
FORVALUES（[boundary_value[,...n]]）
[;
]
分区函数语法的相关解释：
1，创建一个分区函数和创建一个普通的数据库对象（例如表）没什么区别。
所以根据标准语法走就OK了。
2，partition_function_name是分区函数的名称。
分区函数名称在数据库内必须唯一，并且符合标识符的规则。
3，input_parameter_type是用于分区的列的数据类型，习惯把它称为分区依据列。
当用作分区列时，除text、ntext、image、xml、timestamp、varchar（max）、nvarchar（max）、varbinary（max）、别名数据类型或CLR用户定义数据类型外，其他所有数据类型均有效。
分区依据列是在CREATETABLE或CREATEINDEX语句中指定的。
4，boundary_value[,...n]中的boundary_value是边界值（或边界点的值），n代表可以最多有n个边界值，即n指定boundary_value提供的值的数目，但n不能超过999。
所创建的分区数等于n+1。
不必按顺序列出各值。
如果值未按顺序列出，则DatabaseEngine将对这些边界值进行排序，创建分区函数并返回一个警告，说明未按顺序提供值。
如果n包括任何重复的值，则数据库引擎将返回错误。
边界值的取值一定是和分区依据列相关的，所以只能使用CREATETABLE或CREATEINDEX语句中指定的一个分区列。
5，LEFT|RIGHT指定boundary_value[,...n]的每个boundary_value属于每个边界值间隔的哪一侧（左侧还是右侧）。
如果未指定，则默认值为LEFT。
例如我们可以依据某个表的int列来创建分区函数:
createpartitionfunctionMyPF1（int）
rangeleft--默认是left，所以可以省略left
forvalues（500000,1000000,1500000）
很明显，这个分区函数创建了4个分区，因为此时n=3,所以分区总数是n+1=4。
而那个int分区依据列表明将要分区的那个表里面一定有一列是int类型，是分区依据列。
这个分区函数我们用的是rangeleft，各个分区的取值范围如下表：
分区
取值范围
1
（负无穷,500000]
2
[500001,1000000]
3
[1000001,1500000]
4
[1500001,正无穷）
如果换成rangeright，即创建分区函数时代码如下：
rangeright
那么各个分区的取值范围如下表：
（负无穷,499999]
[500000,999999]
[1000000,1499999]
[1500000,正无穷）
我们还可以根据日期列创建分区函数，例如：
createpartitionfunctionMyPF2（datetime）
forvalues（'
2008/01/01'
'
2009/01/01'
）
这个分区函数非常适合查询和归档某一年的数据。
各个分区的取值范围如下表：
<
=2007/12/31
[2008/01/01,2008/12/31]
>
=2009/01/01
当然我们也可以根据月份分区，而分区依据列支持的数据类型非常多，参照项目的实际情况选择最能表示分区的列类型。
分区方案
对表和索引进行分区的第二步是创建分区方案。
分区方案定义了一个特定的分区函数将使用的物理存储结构（其实就是文件组），或者说是分区方案将分区函数生成的分区映射到我们定义的一组文件组。
所以分区方案解决的是Where的问题，即表的各个分区在哪里存储的问题。
分区方案的创建语法如下：
CREATEPARTITIONSCHEMEpartition_scheme_name
ASPARTITIONpartition_function_name
[ALL]TO（{file_group_name|[PRIMARY]}[,...n]）
分区方案语法的相关解释：
1，创建分区方案时，根据分区函数的参数，定义映射表分区的文件组。
必须指定足够的文件组来容纳分区数。
可以指定所有分区映射到不同文件组、某些分区映射到单个文件组或所有分区映射到单个文件组。
如果您希望在以后添加更多分区，还可以指定其他“未分配的”文件组。
在这种情况下，SQLServer用NEXTUSED属性标记其中一个文件组。
这意味着该文件组将包含下一个添加的分区。
一个分区方案仅可以使用一个分区函数。
但是，一个分区函数可以参与多个分区方案。
2，partition_scheme_name是分区方案的名称。
分区方案名称在数据库中必须是唯一的，并且符合标识符规则。
3，partition_function_name是使用当前分区方案的分区函数的名称。
分区函数所创建的分区将映射到在分区方案中指定的文件组。
partition_function_name必须已经存在于数据库中。
4，ALL指定所有分区都映射到在file_group_name中提供的同一个文件组，或映射到主文件组（如果指定了[PRIMARY]）。
如果指定了ALL，则只能指定一个file_group_name。
5，file_group_name|[PRIMARY][,...n]代表n个文件组。
和分区函数中的各个分区对应。
文件组必须已经存在于数据库中。
如果指定了[PRIMARY]，则分区将存储于主文件组中。
分区分配到文件组的顺序是从分区1开始，按文件组在[,...n]中列出的顺序进行分配。
在[,...n]中，可以多次指定同一个文件组。
如果n不足以拥有在分区函数中指定的分区数，则CREATEPARTITIONSCHEME将失败，并返回错误。
6，如果分区函数生成的分区数少于创建分区方案时提供的文件组数，则分区方案中第一个未分配的文件组将被标记为NEXTUSED，并且出现显示命名NEXTUSED文件组的信息。
如果指定了ALL，则单独的文件组将为该分区函数保持它的NEXTUSED属性。
如果在ALTERPARTITIONFUNCTION语句中创建了一个分区，则NEXTUSED文件组将再接收一个分区。
若要再创建一个未分配的文件组来拥有新的分区，请使用ALTERPARTITIONSCHEME。
分区方案例子1：
下面的代码先创建一个分区函数，然后再创建这个分区函数使用的分区方案，这个分区方案将每个分区映射到不同文件组。
代码如下：
asrangeleft
go
createpartitionschemeMyPS1
aspartitionMyPF1
to（fg1,fg2,fg3,fg4）
文件组、分区和分区边界值范围之间的关系如下表：
文件组
fg1
fg2
fg3
fg4
分区方案例子2：
下面的代码先创建一个分区函数，然后再创建这个分区函数使用的分区方案，这个分区方案将多个分区映射到同一个文件组。
createpartitionfunctionMyPF2（int）
createpartitionschemeMyPS2
aspartitionMyPF2
to（fg1,fg1,fg1,fg2）
Fg1
Fg2
分区方案例子3：
下面的代码先创建一个分区函数，然后再创建这个分区函数使用的分区方案，这个分区方案将所有分区映射到同一个文件组。
createpartitionfunctionMyPF3（int）
createpartitionschemeMyPS3
aspartitionMyPF3
allto（fg1）
分区方案例子4：
下面的代码先创建一个分区函数，然后再创建这个分区函数使用的分区方案，这个分区方案指定了“NEXTUSED”文件组。
createpartitionfunctionMyPF4（int）
forvalues（500000,1000000,1500000）--4个分区
createpartitionschemeMyPS4
aspartitionMyPF4
to（fg1,fg2,fg3,fg4,fg5）--5个文件组
那么文件组fg5将自动被标记为“NEXTUSED”文件组。
分区方案例子5：
下面的代码先创建一个分区函数，然后再创建这个分区函数使用的分区方案，这个分区方案指定了“[primary]”文件组。
createpartitionfunctionMyPF5（datetime）
createpartitionschemeMyPS5
aspartitionMyPF5
to（[primary],fg1,fg2）
最后必须明白一点，一张表最多只能有1000个分区。
分区表
在分区函数和分区方案创建完成后，创建分区表的准备工作已经完成。
我们看一个完整的例子，代码如下：
--创建分区函数
createpartitionfunctionMyPF（datetime）
2007-1-1'
2008-1-1'
--创建分区方案
createpartitionschemeMyPS
aspartitionMyPF
to（fg1,fg2,fg3）
--创建分区表
createtableorders
（
OrderIDintidentity（1,1）primarykey,
OrderDatedatetime,
CustIDvarchar（10）
onMyPS（OrderDate）
更完整的例子请关注实战分区表，我会用一个完整的Demo来演示分区表这一技术。
下一节内容包裹：
1，实战分区表
2，查询某个分区
3，增加分区
4，删除分区
5，归档数据
通过上2篇博文，我们了解了分区表的理论，这一节就开始实战。
本篇博文的内容如下：
1，建立分区表
2，查询分区
3，归档数据
4，添加分区
5，删除分区
6，查看元数据
PS下：
最近收到很多朋友的消息和邮件，大多是关于数据库的问题，没有一一答复，由于平时工作比较忙，博客更新的比较慢，在这里说声抱歉。
OK，我们以一个销售数据库场景开始分区表实战。
第一步：
建立我们要使用的数据库，最重要的是建立多个文件组。
CREATEDATABASESalesONPRIMARY
（
NAME=N'
Sales'
FILENAME=N'
C:
\Sales.mdf'
SIZE=3MB,
MAXSIZE=100MB,
FILEGROWTH=10%
）,
FILEGROUPFG1
File1'
\File1.ndf'
SIZE=1MB,
FILEGROUPFG2
File2'
\File2.ndf'
MAXSIZE=100MB,
）,
FILEGROUPFG3
File3'
\File3.ndf'
）
LOGON
Sales_Log'
\Sales_Log.ldf'
FILEGROWTH=10%
GO
第二步：
建立分区函数，这里我们建立三个分区。
how（如何对数据进行分区）
USESales
CREATEPARTITIONFUNCTIONpf_OrderDate（datetime）
ASRANGERIGHT
FORVALUES（'
2003/01/01'
2004/01/01'
）--n不能超过999，创建的分区数等于n+1
第三步：
创建分区方案，关联到分区函数。
where（在哪里对数据进行分区）
GO
CREATEPARTITIONSCHEMEps_OrderDate
ASPARTITIONpf_OrderDate
TO（FG1,FG2,FG3）
第四步：
创建分区表。
创建表并将其绑定到分区方案。
这里我们建立2个表，表的结构一样。
其中OrdersHistory表用于保存归档数据。
CREATETABLEdbo.Orders
OrderIDintidentity（10000,1）,
OrderDatedatetimeNOTNULL,
CustomerIDintNOTNULL,
CONSTRAINTPK_OrdersPRIMARYKEY（OrderID,OrderDate）
ONps_OrderDate（OrderDate）
CREATETABLEdbo.OrdersHistory
CONSTRAINTPK_OrdersHistoryPRIMARYKEY（OrderID,OrderDate）
通过以上四步，我们建立了分区表。
接着我们要插入一些数据，来进行数据归档，分区查询等。
向数据表中写入2002年的范例数据
INSERTINTOdbo.Orders（OrderDate,CustomerID）VALUES（'
2002/6/25'
1000）
2002/8/13'
2002/8/25'
2002/9/23'
1000）
向数据表中写入2003年的范例数据
2003/6/25'
2003/8/13'
2003/8/25'
2003/9/23'
我们可以用下面的代码查询这2表：
SELECT*FROMdbo.Orders
SELECT*FROMdbo.OrdersHistory
查询的结果是Orders里面有8行数据，而OrdersHistory还没有数据。
因为我们还没归档数据，所以OrdersHistory表还没有数据。
插入完数据后，我们来做如下实验：
1，查询某个分区
这里我们要用到$PARTITION函数，这个函数可以帮助我们查询某个分区的数据，还可以检索某个值所隶属的分区号。
$PARTITION函数的进一步细节可以查看MSDN
查询已分区表Order的第一个分区，代码如下：
SELECT*
FROMdbo.Orders
WHERE$PARTITION.pf_OrderDate（OrderDate）=1
查询结
升级会员 