SQL Server partition detailed partition

I. Introduction of SQL Server partitioning

In SQL Server, all tables and indexes of a database are treated as partitioned tables and indexes, and the default tables and index values contain a partition, which means that the table or index contains at least one partition.

In SQL Server, data is partitioned horizontally, and multiple rows of data are mapped to a single partition. A partitioned table or index, which is viewed as a single logical entity when executing a query or update;

In short, partitioning a table data into multiple tables to store, for large data volumes of the table, the table is divided into multiple queries, if only one partition of the data will reduce consumption and improve efficiency. It is important to note that a single index or partition of a table must reside in a database.

When using large amounts of data management, SQL Server uses partitioning to quickly access subsets of data and reduce IO productivity.

At the same time, different partitions can be stored in different filegroups, and if the filegroups can be stored on different logical disks, the concurrent use of IO can be achieved to improve the efficiency. As shown in the following:

ii. SQL Server partition creation

Although partitioning has many advantages, it cannot be used at will, and it does not say that the cumbersome zoning management, just the negative impact of cross-zoning, requires us to analyze the need to use partitioning.

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.

1. 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 FUNCTION partition_function_name (Input_parameter_type)  as [ ]forVALUES[ ] [  ]

1.1 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];CREATEPARTITIONFUNCTION [pf_partiontest01](datetime) asRANGE Right  for VALUES('2014-12-09','2014-12-10'/*partition function Query*/SELECTpf.name partition function name, Case  whenBoundary_value_on_right=1  Then ' Right' ELSE ' Left' ENDpartition bounds, value partition bounds fromSys.partition_functions PF Left JOINSys.partition_range_values PRV onprv.function_id=pf.function_idORDER  byboundary_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?

2. Create a partition schema

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

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

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];CREATEPARTITION SCHEME[pc_partiontest01]  asPARTITION[pf_partiontest01]  to('Primary','PartionTest201412','PartionTest201412')
/*Partition schema Query*/SELECTps.name partition_scheme,ds.name filegroup,pf.name partition_function,pf.type_desc+':'+ Case  whenPf.boundary_value_on_right=0  Then ' Left' Else 'rigth' EndFunction_type fromsys.partition_schemes PSJOINSys.destination_data_spaces DDS onps.data_space_id=dds.partition_scheme_idJOINSys.data_spaces DS ondds.data_space_id=ds.data_space_idJOINSys.partition_functions PF onps.function_id=pf.function_id

The results are as follows:

3. Create a partitioned table

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

4. Create an index partitionIII. SQL Server partition management

SQL Server partition detailed partition