SQL Server Partition Table

Introduction to Partitioned Tables

The partitioned table is a new concept introduced by SQL Server2005, which logically divides a table physically into multiple parts . (That is, it allows a table to be stored on a different physical disk). Before SQL Server2005, a partitioned table is actually a distributed view, which is a union operation of multiple tables.

A partitioned table is logically a table and physically multiple tables. In the user's view, the partition table and the normal table are the same, the user angle does not feel.

　　

Before SQL Server2005, because there is no concept of partitioning, the so-called partitioning is just a distributed view:

　　　

reasons for partitioning the table

　　Table partitioning, which is only available for SQL Server Enterprise or SQL Server development, understands the concepts of files and filegroups in SQL Server before understanding the concept of table partitioning. This article is for interpreting files and filegroups. Http://www.cnblogs.com/kissdodog/p/3156166.html

Table partitioning is primarily used for:

• Provide performance: This is the purpose of the majority of the partition, a table division to different hard disks or other storage media, will greatly improve the query speed.
• Improved Stability: When a partition is out of the question, it will not affect the other partitions, only the current bad partition is not available.
• Easy to manage: Divide a large table into small tables, then backup and restore no longer need to back up the entire table, you can back up the partition separately.
• Archive: Store Some less-commonly used data separately. For example, the data records of 1 years ago are divided into a dedicated archiving server storage.

Procedures for Partitioning tables

The partitioning table is divided into three steps:

1. 1. Defining partition Functions
   2. Defining the partition Architecture
   3. Defining partition Tables

　　

partition functions, partition schemas and partitioned tables are related to the following: partitioned tables depend on partition architectures, and partition architectures depend on partition functions. 　　Therefore, the order in which the partitioned tables are defined is basically the definition partition function, the definition partition frame, and so on. In practice, define a table that needs to be partitioned:

We use the Salesdate column as the partition column.

The first step is to define the partition function:

A partition function is used to determine which partition a row of data belongs to, and to set the boundary value in a partition function to determine its partition based on the value of a particular column in the row. such as the partition table above, you can set the value of Salesdate to determine its different partitions, join us to define the Salesdate two boundary values for partitioning, then three partitions will be generated, now set two boundary values for 2004-01-01 and 2007-01-01, respectively, The above table can be divided into three partitions based on these two boundary values.

(Careyson Daniel gives the figure, really put easy to understand.) )

The syntax for defining a partition function is as follows:

FUNCTION partition_function_name (input_parameter_type)[[] []    

In the prototype syntax for defining partition functions above, we see that there is no specific table involved because the partition function is not bound to a specific table. In addition, you can see range left and right in the prototype. This parameter determines the threshold value (that is, the value equal to the cutoff value that is exactly equal to 2004-01-01 or 2007-01-01) should be left or right.

　　

To create a partition function:

--Create partition function   fnpartition (DATE) right VALUES ('2004-01-01','  2007-01-01') --see if the partition table is created successfully from sys.partition_functions      

The above query statement shows the following results:

　　

From the system view, you can see that this partition function has been created successfully.

The second step is to define the partitioning framework

After defining the partition function, you only know that the data is assigned to a different partition based on the value of the column. The partitioning architecture is required to define the storage for each partition.

Partition Architecture Syntax Prototype:

CREATE PARTITION SCHEME partition_scheme_name as PARTITION partition_function_name [[[] ) []         

From the prototype point of view, the partition architecture is only dependent on partition functions. The Partitioning framework is responsible for assigning which filegroup each zone belongs to, and the partition function determines which data belongs to which partition.

　　

Create a partition structure based on the previously created partition function:

　　-- based on the previous partition function to create the partition framework schema  PARTITION SCHEME schemaforparirion -This is the partition function previously created to (filegroup1,<　　c8>[Primary--filegroup1 is a filegroup that you add yourself, because there are two cutoff values and 3 partitions, so to specify 3 filegroups, you can also use all so-called partitions to point to a filegroup -- View the created partition architecture From sys.partition_schemes  　　       

The above SELECT statement outputs the following results:

　　

Notice that the partition architecture was created successfully.

Step three: Define the partition table

With partition functions and partition architecture, you can create a partitioned table, and the table will be created to determine whether it is a partitioned table. Although in most cases, it is only when the table is too large to think about partitioning. However , a partitioned table can only be specified as a partitioned table when it is created.

TABLE orderrecords (    int,    int, SalesDate Date) --schemaforpartition is the partition schema just defined, in parentheses For the specified partition column     

Then manually add 3 data to the database:

　　

Then execute the query:

SelectConvertvarchar(), Ps.name)AsPartition_scheme,p.partition_number,Convertvarchar, Ds2.name)AsFilegroupConvertvarchar19),IsNull (V.value,‘‘),120)AsRange_boundary,STR (p.rows,9)AsRowsFromSys.indexes IJoin Sys.partition_schemes PSOn i.data_space_id=ps.data_space_idJoinSys.destination_data_spaces DDSOn ps.data_space_id=dds.partition_scheme_idJoin Sys.data_spaces DS2On dds.data_space_id=ds2.data_space_idJoin Sys.partitions POn dds.destination_id=P.partition_numberand P.object_id= i.object_idand p.index_id=i.index_idJoin Sys.partition_functions PFOn ps.function_id=pf.function_idLeftJOIN sys. Partition_range_values VOn pf.function_id=v.function_id andv.boundary_id = p.partition_number - pf.boundary_value_on_right WHERE I.  object_id = object_id ('orderrecords') --Here is the table name and i.index_id in (0, 1 ) ORDER by P.partition_number           

As you can see, the partition works:

　　

Partition Table Segmentation

Partition table segmentation, the equivalent of creating a new partition, the original partition needs to split the content into a new partition, and then delete the contents of the old partition. New Add one more split point: 2009-01-01. As shown in the following:

For the operation, if split partition 3 has content to be divided into partition 4, then the data needs to be copied to partition 4, and delete the corresponding data on partition 3.

　　This operation consumes IO very very much and locks the contents of partition 3 during the split, causing the contents of partition 3 to be temporarily unavailable. Moreover, the log content generated by this operation will be 4 times times the transferred data.

Therefore, the best time to build a table, it is necessary to consider the future of the division, such as pre-award to 2014-01-01, 2016-01-01.

Splitting an existing partition requires two steps:

1. First, tell SQL Server which filegroup to put the newly created partition into
2. Create a new split point.

Add a piece of data, resulting in the original table as follows:

　　

Execute that long query, as shown below:

　　

Now, you can perform the split operation:

　　--split partition data exists in which filegroup   'PRIMARY' --add split point FUNCTION fnpartition () split RANGE (' 2009-01-01')        

After execution, look at the results as follows:

　　

merging of partitioned tables

The merging of partitions can be done by the inverse operation of partition division. A partition's merge needs to provide a split point, and the split point must already exist in the existing split table, or it will be an error when merging.

For example, for the above example, the merger is based on 2009-01-01:

　　

Merge partition operations:

--Provide split point, merge partition  FUNCTION fnpartition () Merge RANGE ('2009-01-01')    

Then look at the partition information:

It should be noted here that if a partition has been merged, will the file be a filegroup with partition 3 or a filegroup of partition 4? This depends on whether the partition function we defined at the beginning is left or right. If left is defined, then partition 3 on the other side is merged into Partition 4. If it is right, partition 4 to partition 3 is merged to the left.

Finally, I enclose a sentence: Careyson Daniel, I love you.

This article learns from: http://www.cnblogs.com/CareySon/archive/2011/12/30/2307766.html

SQL Server Partition Table