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

　　

From SQL Server Profiler you can see the following script to create the database:

CREATE DATABASE [Northwind] containment = NONE on PRIMARY (NAME = n ' Northwind ', FILENAME = N ' F:\Database\Northwind\North Wind.mdf ', SIZE = 5120KB, filegrowth = 1024KB), FILEGROUP [SECTION2010] (NAME = n ' northwind_data_2010 ', FILENAME = N ') F:\Database\Northwind\Northwind_Data_2010.ndf ', SIZE = 5120KB, filegrowth = 1024KB), FILEGROUP [SECTION2011] (NAME =  N ' northwind_data_2011 ', FILENAME = N ' F:\Database\Northwind\Northwind_Data_2011.ndf ', SIZE = 5120KB, filegrowth = 1024KB  ), FILEGROUP [SECTION2012] (NAME = n ' northwind_data_2012 ', FILENAME = n ' F:\Database\Northwind\Northwind_Data_2012.ndf ' , SIZE = 5120KB, filegrowth = 1024KB), FILEGROUP [SECTION2013] (NAME = n ' northwind_data_2013 ', FILENAME = N ' F:\Databa Se\northwind\northwind_data_2013.ndf ', SIZE = 5120KB, filegrowth = 1024KB), FILEGROUP [SECTION2014] (NAME = N ' Northwi nd_data_2014 ', FILENAME = N ' F:\Database\Northwind\Northwind_Data_2014.ndf ', SIZE = 5120KB, filegrowth = 1024KB) LOG on (NAME = N ' Northwind_log ', FILENAME = N ' F:\Database\Northwind\Northwind_log.ldf ', SIZE = 2048KB, filegrowth = 10%) 

To view the database filegroup SQL statement:

2.3> Creating a partition function

To create a partition function Transact-SQL syntax:

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

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 PARTITION FUNCTION function_datetime (DateTime) as RANGE rightfor VALUES (' 2011-01-01 ', ' 2012-01-01 ', ' 2013-01-01 ' , ' 2014-01-01 ')

After you have finished creating the partition function, you can view the created partition function with the following SQL statement.

SELECT * from Sys.partition_functions

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_nameas PARTITION partition_function_name[All "to ({file_group_name | [PRIMARY]} [,... N]) [ ; ]

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_datetimeas PARTITION function_datetimeto (SECTION2010, SECTION2011, SECTION2012, SECTION2013, SECTION2014)

After you create the partition function and partition scheme, you can view it in the storage of the database:

You can view the created partition scheme by using the following SQL statement:

SELECT * from Sys.partition_schemes

2.5> Creating a partitioned table

CREATE TABLE [Order] (    OrderID int IDENTITY () not NULL,    UserID INT not NULL,    TotalAmount DECIMAL (18,2) null ,    OrderDate DATETIME not NULL) on Scheme_datetime (OrderDate)

It is important to note that partitioned tables are no longer able to create clustered indexes because clustered indexes can store records in a physical order, whereas partitioned tables store data in different tables, which are conflicting, so you can no longer create a clustered index when you create a partitioned table.

After you finish creating the order table, look at the properties of the table and you can see that the order table is already a partitioned table.

3. Operation Partition Table

3.1> Insert Data

 use [Northwind]goinsert into [dbo]. [Order] ([Userid],[totalamount], [OrderDate]) VALUES (1, 10.00, ' 2009-10-20 '); INSERT into [dbo]. [Order] ([Userid],[totalamount], [OrderDate]) VALUES (1, 20.50, ' 2009-12-31 '); INSERT into [dbo]. [Order] ([Userid],[totalamount], [OrderDate]) VALUES (2, 40.00, ' 2010-01-20 '); INSERT into [dbo]. [Order] ([Userid],[totalamount], [OrderDate]) VALUES (3, 40.00, ' 2010-10-20 '); INSERT into [dbo]. [Order] ([Userid],[totalamount], [OrderDate]) VALUES (4, 50.00, ' 2011-10-20 '); INSERT into [dbo]. [Order] ([Userid],[totalamount], [OrderDate]) VALUES (5, 60.00, ' 2012-10-20 '); INSERT into [dbo]. [Order] ([Userid],[totalamount], [OrderDate]) VALUES (5, 60.00, ' 2012-10-20 '); INSERT into [dbo]. [Order] ([Userid],[totalamount], [OrderDate]) VALUES (6, 70.00, ' 2013-10-20 '); INSERT into [dbo]. [Order] ([Userid],[totalamount], [OrderDate]) VALUES (Ten, 90.00, ' 2014-10-20 '); INSERT into [dbo]. [Order] ([Userid],[totalamount], [OrderDate]) VALUES (9, 100.00, ' 2015-10-20 '); GO 

3.2> the physical partition table where the data is queried

Using a generic SELECT statement in a partitioned table does not know which of the different physical tables the data is stored in, and to know which physical table the data is stored in, you can use the $partition function, which can call the partition function and return the number of the physical partition where the data resides.

Syntax for $PARTITION: $PARTITION. Partition function name (expression)

SELECT $PARTITION. Function_datetime (' 2010-01-01 ')

The query result partition function returns 1, indicating that 2010-01-01 data is stored in the 1th physical partition table.

You can use the $partition function to know exactly which records are stored in each physical partition table.

To view the records stored in the Physical partition table:

SELECT * FROM [Order] WHERE $PARTITION. Function_datetime (OrderDate) = 1

SELECT $PARTITION. Function_datetime (OrderDate) as partition number, COUNT (1) as Record count from [Order]group by $PARTITION. Function_datetime (OrderDate)

3.3> Modifying partitioned table data

UPDATE dbo. [Order] SET orderdate= ' 2015-01-01 ' WHERE OrderID = 3

4. Converting a normal table to a partitioned table

The normal table is a clustered index on the primary key, and the physical location of the record is determined by the primary key.

Example: Creating a product normal table

CREATE TABLE Product (    ProductID INT IDENTITY) not NULL,    ProductName VARCHAR (+) NOT NULL,    UnitPrice DECIMAL (18,2) NULL,    createdate DATETIME not NULL,    CONSTRAINT pk_product PRIMARY KEY CLUSTERED (ProductID))

Use [Northwind]goinsert into [dbo]. [Product] ([Productname],[unitprice],[createdate]) VALUES (' LINQ to SQL ', ' 2012-01-01 '); INSERT into [dbo]. [Product] ([Productname],[unitprice],[createdate]) VALUES (' LINQ to XML ', ' 2012-12-01 '); INSERT into [dbo]. [Product] ([Productname],[unitprice],[createdate]) VALUES (' LINQ to Object ', ten, ' 2013-02-01 '); INSERT into [dbo]. [Product] ([Productname],[unitprice],[createdate]) VALUES (' LINQ to ADO ', ' 2014-01-02 '); INSERT into [dbo]. [Product] ([Productname],[unitprice],[createdate]) VALUES (' LINQ to Entity ', 10, ' 2015-01-01 '); GO

To view the properties of a table product:

View the index of the table product, and you can see that pk_product is a clustered index.

The operation to convert a normal table to a partitioned table is to first delete the clustered index on the normal table and create a new clustered index that uses the partitioning scheme in the clustered index.

In SQL Server, the clustered index is created by default on the primary key field, and the clustered index of the primary key is removed.

ALTER TABLE Product DROP CONSTRAINT pk_product

Re-create the primary key nonclustered index

ALTER TABLE Product ADD CONSTRAINT pk_product PRIMARY KEY nonclustered (ProductID ASC)

Re-created PRIMARY key:

To create a clustered index using a partitioning scheme:

CREATE CLUSTERED INDEX ix_createdate on Product (createdate) on Scheme_datetime (CreateDate)

Adjusted Product Table Properties:

Physical preservation of Adjusted product table records:

5. Delete (merge) a partitioned table

　　To delete the 2012-01-01 partition, modify the partition function:

ALTER PARTITION FUNCTION function_datetime () MERGE RANGE (' 2012-01-01 ')

After the partition function has been modified, the partition scheme associated with it will also be automatically adjusted. After performing the functions for merging partitions above, look at the Create script for the partitioning scheme.

CREATE PARTITION SCHEME [scheme_datetime] as PARTITION [Function_datetime] to ([SECTION2010], [SECTION2011], [ SECTION2013], [SECTION2014])

After the partition is merged, the merged partition record is also reassigned to the physical save location.

6. Adding partitions

The number of filegroups specified in the partition scheme is 1 larger than the specified bounds in the score area function, and when an available filegroup is specified for a partition scheme, the partition scheme does not immediately use the filegroup, except that the filegroup is first reserved, and the partition scheme will not use this filegroup until the partition function has been modified. If the partition function does not change, the number of filegroups in the partition scheme does not change.

The filegroups that you need to add partitions can use SECTION2012 that were not reused after the partition was merged, or you can create a new filegroup.

ALTER DATABASE [Northwind] ADD FILEGROUP [SECTION2015]

ALTER DATABASE [Northwind] ADD FILE (     NAME = n ' northwind_data_2015 ',     FILENAME = N ' F:\Database\Northwind\ Northwind_data_2015.ndf ',     SIZE = 5120KB,     filegrowth = 1024KB) to FILEGROUP [SECTION2015]

Specify an available filegroup for the partition scheme:

ALTER PARTITION SCHEME scheme_datetime NEXT used [SECTION2015]

Modify the partition function to add a partition:

ALTER PARTITION FUNCTION function_datetime () SPLIT RANGE (' 2015-01-01 ')

To view the physical storage of data after the partition is added:

SQL Server series: Partitioning table operations