SQL SERVER分区函数Word文件下载.docx

1、一般而言，衡量大型表是以数据为标准的，但对于适合分区的大型表，衡量大型表更重要的是对数据访问的性能，如果对于某些表的访问和维护有较严重的性能问题，就可以视为大型表，就应该考虑通过更好的设计和分区来解决性能问题。创建分区表必须经过如下三个步骤：1， 创建分区函数2， 创建映射到分区函数的分区方案3， 创建使用该分区方案的分区表分区函数分区函数是数据库中的一个独立对象，它将表的行映射到一组分区，所以分区函数解决的是HOW的问题，即表如何分区的问题。创建分区函数时，必须指明数据分区的边界点以及分区依据列，这样便知道如何对表或索引进行分区。分区函数的创建语法如下：CREATE PARTITION FU

2、NCTION partition_function_name （ input_parameter_type ）AS RANGE LEFT | RIGHT FOR VALUES （ boundary_value ,.n ） ; 分区函数语法的相关解释：1， 创建一个分区函数和创建一个普通的数据库对象（例如表）没什么区别。所以根据标准语法走就OK了。2， partition_function_name是分区函数的名称。分区函数名称在数据库内必须唯一，并且符合标识符的规则。3， input_parameter_type是用于分区的列的数据类型，习惯把它称为分区依据列。当用作分区列时，除 text、n

3、text、image、xml、timestamp、varchar（max）、nvarchar（max）、varbinary（max）、别名数据类型或 CLR用户定义数据类型外，其他所有数据类型均有效。分区依据列是在 CREATE TABLE或 CREATE INDEX语句中指定的。4， boundary_value ,.n 中的boundary_value是边界值（或边界点的值），n代表可以最多有n个边界值，即n指定 boundary_value提供的值的数目，但n不能超过 999。所创建的分区数等于 n + 1。不必按顺序列出各值。如果值未按顺序列出，则 Database Engine将对这

4、些边界值进行排序，创建分区函数并返回一个警告，说明未按顺序提供值。如果 n包括任何重复的值，则数据库引擎将返回错误。边界值的取值一定是和分区依据列相关的，所以只能使用 CREATE TABLE或 CREATE INDEX语句中指定的一个分区列。5， LEFT | RIGHT 指定boundary_value ,.n 的每个boundary_value属于每个边界值间隔的哪一侧（左侧还是右侧）。如果未指定，则默认值为 LEFT。例如我们可以依据某个表的int列来创建分区函数:create partition function MyPF1（int）range left -默认是left，所以可以省

5、略leftfor values（500000,1000000,1500000）很明显，这个分区函数创建了4个分区，因为此时n=3,所以分区总数是n+1=4。而那个int分区依据列表明将要分区的那个表里面一定有一列是int类型，是分区依据列。这个分区函数我们用的是range left，各个分区的取值范围如下表：分区取值范围1（负无穷,5000002500001,100000031000001,150000041500001,正无穷）如果换成range right，即创建分区函数时代码如下：range right那么各个分区的取值范围如下表：（负无穷,499999500000,9999991000

6、000,14999991500000,正无穷）我们还可以根据日期列创建分区函数，例如：create partition function MyPF2（datetime）for values（2008/01/01, 2009/01/01）这个分区函数非常适合查询和归档某一年的数据。各个分区的取值范围如下表：=2009/01/01当然我们也可以根据月份分区，而分区依据列支持的数据类型非常多，参照项目的实际情况选择最能表示分区的列类型。分区方案对表和索引进行分区的第二步是创建分区方案。分区方案定义了一个特定的分区函数将使用的物理存储结构（其实就是文件组），或者说是分区方案将分区函数生成的分区映射到我

7、们定义的一组文件组。所以分区方案解决的是Where的问题，即表的各个分区在哪里存储的问题。分区方案的创建语法如下：CREATE PARTITION SCHEME partition_scheme_nameAS PARTITION partition_function_name ALL TO （ file_group_name | PRIMARY ,.n ）分区方案语法的相关解释：1， 创建分区方案时，根据分区函数的参数，定义映射表分区的文件组。必须指定足够的文件组来容纳分区数。可以指定所有分区映射到不同文件组、某些分区映射到单个文件组或所有分区映射到单个文件组。如果您希望在以后添加更多分区，还

8、可以指定其他“未分配的”文件组。在这种情况下，SQL Server用 NEXT USED属性标记其中一个文件组。这意味着该文件组将包含下一个添加的分区。一个分区方案仅可以使用一个分区函数。但是，一个分区函数可以参与多个分区方案。2， partition_scheme_name是分区方案的名称。分区方案名称在数据库中必须是唯一的，并且符合标识符规则。3， partition_function_name是使用当前分区方案的分区函数的名称。分区函数所创建的分区将映射到在分区方案中指定的文件组。partition_function_name必须已经存在于数据库中。4， ALL指定所有分区都映射到在 f

9、ile_group_name中提供的同一个文件组，或映射到主文件组（如果指定了 PRIMARY）。如果指定了 ALL，则只能指定一个 file_group_name。5， file_group_name | PRIMARY ,.n代表n个文件组。和分区函数中的各个分区对应。文件组必须已经存在于数据库中。 如果指定了 PRIMARY，则分区将存储于主文件组中。分区分配到文件组的顺序是从分区 1开始，按文件组在 ,.n中列出的顺序进行分配。在 ,.n中，可以多次指定同一个文件组。如果 n不足以拥有在分区函数中指定的分区数，则 CREATE PARTITION SCHEME将失败，并返回错误。6，

10、如果分区函数生成的分区数少于创建分区方案时提供的文件组数，则分区方案中第一个未分配的文件组将被标记为 NEXT USED，并且出现显示命名 NEXT USED文件组的信息。如果指定了 ALL，则单独的文件组将为该分区函数保持它的NEXT USED属性。如果在 ALTER PARTITION FUNCTION语句中创建了一个分区，则 NEXT USED文件组将再接收一个分区。若要再创建一个未分配的文件组来拥有新的分区，请使用 ALTER PARTITION SCHEME。分区方案例子1：下面的代码先创建一个分区函数，然后再创建这个分区函数使用的分区方案，这个分区方案将每个分区映射到不同文件组。代

11、码如下：as range leftgocreate partition scheme MyPS1as partition MyPF1to （fg1, fg2, fg3, fg4）文件组、分区和分区边界值范围之间的关系如下表：文件组fg1fg2fg3fg4分区方案例子2：下面的代码先创建一个分区函数，然后再创建这个分区函数使用的分区方案，这个分区方案将多个分区映射到同一个文件组。create partition function MyPF2（int）create partition scheme MyPS2as partition MyPF2to （fg1, fg1, fg1, fg2）Fg1F

12、g2分区方案例子3：下面的代码先创建一个分区函数，然后再创建这个分区函数使用的分区方案，这个分区方案将所有分区映射到同一个文件组。create partition function MyPF3 （int）create partition scheme MyPS3as partition MyPF3all to （fg1）分区方案例子4：下面的代码先创建一个分区函数，然后再创建这个分区函数使用的分区方案，这个分区方案指定了“NEXT USED”文件组。create partition function MyPF4（int）for values（500000,1000000,1500000） -4

13、个分区create partition scheme MyPS4as partition MyPF4to （fg1, fg2, fg3, fg4, fg5） -5个文件组那么文件组fg5将自动被标记为“NEXT USED”文件组。分区方案例子5：下面的代码先创建一个分区函数，然后再创建这个分区函数使用的分区方案，这个分区方案指定了“primary”文件组。create partition function MyPF5（datetime）create partition scheme MyPS5as partition MyPF5to（primary, fg1, fg2）最后必须明白一点，一张表

14、最多只能有1000个分区。分区表在分区函数和分区方案创建完成后，创建分区表的准备工作已经完成。我们看一个完整的例子，代码如下：-创建分区函数create partition function MyPF（datetime）2007-1-12008-1-1-创建分区方案create partition scheme MyPSas partition MyPFto（fg1, fg2, fg3）-创建分区表create table orders（ OrderID int identity（1,1） primary key, OrderDate datetime, CustID varchar（10）o

15、n MyPS（OrderDate）更完整的例子请关注实战分区表，我会用一个完整的Demo来演示分区表这一技术。下一节内容包裹：1， 实战分区表2， 查询某个分区3， 增加分区4， 删除分区5， 归档数据通过上2篇博文，我们了解了分区表的理论，这一节就开始实战。本篇博文的内容如下：1，建立分区表2，查询分区3，归档数据4，添加分区5，删除分区6，查看元数据PS下：最近收到很多朋友的消息和邮件，大多是关于数据库的问题，没有一一答复，由于平时工作比较忙，博客更新的比较慢，在这里说声抱歉。OK，我们以一个销售数据库场景开始分区表实战。第一步：建立我们要使用的数据库，最重要的是建立多个文件组。CREAT

16、EDATABASESales ON PRIMARY（ NAME = NSales, FILENAME = NC:Sales.mdf SIZE = 3MB, MAXSIZE = 100MB, FILEGROWTH = 10% ）, FILEGROUP FG1 File1File1.ndf SIZE = 1MB, FILEGROUP FG2 File2File2.ndf MAXSIZE = 100MB,）,FILEGROUP FG3 File3File3.ndf） LOGON Sales_LogSales_Log.ldf FILEGROWTH = 10%GO第二步：建立分区函数，这里我们建立三个分

17、区。 how（如何对数据进行分区）USESales CREATEPARTITION FUNCTION pf_OrderDate （datetime） ASRANGE RIGHT FORVALUES（2003/01/012004/01/01） -n不能超过 999，创建的分区数等于 n + 1第三步：创建分区方案，关联到分区函数 。 where（在哪里对数据进行分区）GO CREATEPARTITION SCHEME ps_OrderDate ASPARTITION pf_OrderDate TO（FG1, FG2, FG3） 第四步：创建分区表。创建表并将其绑定到分区方案。这里我们建立2个表，

18、表的结构一样。其中OrdersHistory表用于保存归档数据。CREATETABLEdbo.Orders OrderID int identity（10000,1）, OrderDate datetime NOT NULL, CustomerID int NOT NULL, CONSTRAINT PK_Orders PRIMARY KEY （OrderID, OrderDate） ONps_OrderDate （OrderDate） CREATETABLEdbo.OrdersHistory CONSTRAINT PK_OrdersHistory PRIMARY KEY （OrderID, O

19、rderDate） 通过以上四步，我们建立了分区表。接着我们要插入一些数据，来进行数据归档，分区查询等。向数据表中写入2002年的范例数据INSERTINTOdbo.Orders （OrderDate, CustomerID） VALUES （2002/6/25, 1000） 2002/8/132002/8/252002/9/23, 1000）向数据表中写入2003年的范例数据2003/6/252003/8/132003/8/252003/9/23我们可以用下面的代码查询这2表：SELECT* FROM dbo.Orders SELECT* FROM dbo.OrdersHistory查询的结果是Orders里面有8行数据，而OrdersHistory还没有数据。因为我们还没归档数据，所以OrdersHistory表还没有数据。插入完数据后，我们来做如下实验：1，查询某个分区这里我们要用到$PARTITION 函数，这个函数可以帮助我们查询某个分区的数据，还可以检索某个值所隶属的分区号。$PARTITION 函数的进一步细节可以查看MSDN查询已分区表Order的第一个分区，代码如下：SELECT*FROMdbo.OrdersWHERE$PARTITION.pf_OrderDate（OrderDate） = 1查询结