SQL Server series: Partitioning table operations

1. Introduction to Partitioned Tables

A partitioned table is logically a table and physically multiple tables. From the user's point of view, the partition table and the normal table are the same. The primary purpose of using partitioned tables is to improve the scalability and manageability of large tables and tables with multiple access patterns.

A partitioned table is a set of data that is partitioned into regions that are stored in different filegroups, using partitions to quickly and efficiently manage and access subsets of data.

1.1> Suitable for partitioning tables

◊ A table in the database a lot of data, in the query data will obviously feel very slow, this time need to consider the partition table;

◊ data is segmented, such as year-delimited data, for the current year's data are often used to increase and revise the operation, and for previous years of data almost do not operate or only do query operations, this situation can use the partition table. The operation of the data if only a subset of data is involved, but not all of the data can consider the partition table, if the data of a table is often used and regardless of the year and other factors often to change the operation is best not to partition.

Advantages of 1.2> Partitioned tables

◊ A partitioned table can physically divide a large table into several small tables, but logically it is a large table.

◊ for systems with multiple CPUs, partitioning can perform parallel operations on the table, which can improve access performance.

2. Create a partitioned table step

The steps to create a partitioned table are 5 steps:

(1) Creating a database filegroup

(2) Create a database file

(3) Creating a partition function

(4) Create a partition scheme

(5) Creating a partitioned table

2.1> Creating a database file group

New sample database Northwind, create database filegroups and files, add filegroups.

　　

2.2> Creating a database file

Create a data file and assign a filegroup to the data file.

　　

Complete the database file information after creation

　　

2.3> Creating a partition function

To create a partition function Transact-SQL syntax:

CREATE FUNCTION partition_function_name (Input_parameter_type)  as [] for VALUES [] [ ]

Parameters:

Partition_function_name: The name of the partition function. The partition function name must be unique within the database and conform to the rules of the identifier.

Input_parameter_type: The data type of the column used for partitioning. When used as a partitioning column, except for text, ntext, image, XML, timestamp, varchar (max), nvarchar (max), varbinary (max), alias data types, or CLR user-defined data types, All data types are valid.

Boundary_value: Specifies the boundary value for each partition that uses Partition_function_name's partitioned table or index. If Boundary_value is empty, the partitioning function enables Partition_function_name to map an entire table or index to a single partition. Only one of the partition columns specified in the Create TABLE or create INDEX statement can be used.

Left | Right specifies when the interval value is sorted from left to right in ascending order by the database engine, Boundary_value [,... n] belongs to the side of each boundary value interval (left or right). If not specified, the default value is left.

Example: Creating a partition function that will be used for the order table

CREATE FUNCTION DATETIME as right forVALUES('2011-01-01'  2012-01-01'2013-01-01 '  2014-01-01')

2.4> Creating a partition scheme

The purpose of a partitioning scheme is to map partitions generated by partition functions into filegroups, which are the partitioning scheme where SQL Server places the partitioned data in which filegroup.

Creates a scenario in the current database that maps partitions of partitioned tables or partitioned indexes to filegroups. The number and domain of partitions for partitioned tables or indexes are determined in the partition function. You must first create a partition function in the Create PARTITION function statement before you can create a partition scheme.

To create the Transact-SQL syntax for a partition scheme:

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

Parameters:

　　partition_scheme_name: The name of the partition scheme. The partition scheme name must be unique within the database and conform to the rules for identifiers.

　　partition_function_name: The name of the partition function that uses the partitioning scheme. partitions created by the partition function are mapped to the filegroup specified in the partition scheme. Partition_function_name must already exist in the database. A single partition cannot contain both FILESTREAM and non-FILESTREAM filegroups.

　　All: Specifies that all partitions are mapped to the filegroup provided in File_group_name, or to the primary filegroup if [PRIMARY] is specified. If all is specified, only one file_group_name can be specified.

　　File_group_name | [PRIMARY] [,... N] : Specifies the name of the filegroup used to hold the partition specified by Partition_function_name. File_group_name must already exist in the database.
If [PRIMARY] is specified, the partition is stored in the primary filegroup. If all is specified, only one file_group_name can be specified. The order in which partitions are assigned to filegroups starts with partition 1 and is allocated in the order listed in [,... N] of filegroups. in [,... n], you can specify the same file_group_name multiple times. If n is not sufficient to have the number of partitions specified in Partition_function_name, the CREATE partition SCHEME will fail with an error.
If partition_function_name generates fewer partitions than the number of filegroups, the first unassigned filegroup is labeled next used, and information appears that displays the named next used filegroup. If all is specified, a separate file_group_name will keep its NEXT used property for that partition_function_name. If a partition is created in the ALTER PARTITION FUNCTION statement, the NEXT used filegroup will receive another partition. To create another unallocated filegroup to own the new partition, use ALTER PARTITION SCHEME.
When you specify a primary filegroup in file_group_name[1,... n], you must separate PRIMARY as it does in [PRIMARY], because it is a keyword.

Example: Create a partition scheme that will be used for the order table

CREATE PARTITION SCHEME Scheme_order  as PARTITION Function_order  to (SECTION2010, SECTION2011, SECTION2012, SECTION2013, SECTION2014)

　　

SQL Server series: Partitioning table operations