(ii) SQL Server partition creation process

Although the partitioning has many advantages (a) SQL Server partition partition, but not at will, and do not say that the complexity of partition management, only the negative impact of cross-partition, we need to carefully analyze whether it is necessary to use the partition. General partition created business features: used for statistics, less use of historical data, data self-growth, possible data redundancy, large amount of data volume insertion. before determining if a partition is appropriate to use, you need to understand how the partition was created, and the partition creation includes:

1. New Partition function

2. Create new files and filegroups

3. New Partition architecture

4. Create a new partition table or partition index

As shown in: The partitioning function defines how partitions are partitioned, partition schemas use partition functions and filegroups, determine partitioning schemes, and tables or indexes use partition schemas to implement partitioning. Between them is the use of relationships, a one-to-many relationship.

First, create a partition function

The partition function defines how the rows of a table or index are mapped to a specified partition based on the values of some columns . The partitioning function develops the partitioning method. all data types that are valid for use as index columns can be used as partitioning columns, except for timestamp. You cannot specify ntext, text, image, XML, varchar (max), nvarchar (max), or varbinary (max) data types as partition-based columns. The basic syntax is as follows:

CREATE PARTITION FUNCTION partition_function_name (input_parameter_type) as RANGE [left | Right]for VALUES ([Boundary_value [,... N]]) [;]

Left/right bounds range [Ieft | Right]

Specify left and right to determine whether the boundary value is on the left or the far side. As shown in range right, the bounds value belongs to the left.

/* New partition function */use [partiontest]; CREATE PARTITION function [pf_partiontest01] (datetime) as RANGE right for VALUES (' 2014-12-09 ', ' 2014-12-10 '/* Partition function Query */se Lectpf.name partition function name, case is boundary_value_on_right=1 then ' right ' ELSE ' left ' END partition bounds way, value partition bounds values from sy S.partition_functions pfleft JOIN sys.partition_range_values prv on prv.function_id = Pf.function_idorder by boundary_id

The query results are as follows:

Attention:

1, most of the business is to use range right, the boundary is kept in the latest partition, after all, the left is the late growth of data; For example, according to the daily partition logic is the day's data exists in the partition of the day, if the day partition is 2014-12-08 00:00.000, range right will 2014-12-08 00:00.000 of the data to 2014-12-08, range Left can only attribute this limit time to 2014-12-07. There is a certain difference from logic.

2, since there is a partition boundary problem, when merging partitions, the specified partition is left or right to merge?

Second, create the partition schema

The partition schema maps the partition specified by the partition function to the filegroup;

CREATE PARTITION SCHEME partition_scheme_nameas PARTITION partition_function_name[All "to ({file_group_name | [PRIMARY]} [,... N]) [ ; ]

A partition specifies that a filegroup is one more than a partition boundary, and the partition schema specifies which filegroup the specific partition data resides on. As shown in the following:

Create specific files and filegroups if necessary before you create the partition schema:

1, new different file groups if stored in different logical disk can improve the IO concurrency capability;

2, at the same time, different files can improve the capacity of disaster tolerance, in the case of a file is bad, other files can continue to use.

3, separate file storage, can also achieve different partitions independent backup, improve the data recovery rate.

Create the following partition schema according to the new partition function "pf_partiontest01" and the default existing filegroup:

/* New partition Schema */use [partiontest]; CREATE PARTITION SCHEME [pc_partiontest01] as PARTITION [pf_partiontest01] to (' Primary ', ' PartionTest201412 ', ' PartionTest201412 ')
/* Partition schema query */selectps.name partition_scheme,ds.name filegroup,pf.name partition_function,pf.type_desc+ ': ' +case When Pf.boundary_value_on_right=0 then ' left ' else ' rigth ' end function_typefrom sys.partition_schemes psjoin Sys.destination_data_spaces DDS on ps.data_space_id=dds.partition_scheme_idjoin sys.data_spaces ds on Dds.data_ space_id=ds.data_space_idjoin sys.partition_functions pf on ps.function_id=pf.function_id

The results are as follows:

Third, create the partition table

As shown, just make the partition schema and the partitioning column.

Iv. Creating an index partition

(ii) SQL Server partition creation process