SQL Server Table Partitioning

What is a table partition

In general, when we create a database table, the table data is stored in a file.

However, if it is a partitioned table, the table data will be divided into different files according to the rules you specify, splitting a large data file into multiple small files, and placing the small files on different disks for processing by multiple CPUs. This reduces the size of the file as it is split, and the hardware system strengthens it, which is naturally beneficial to our operational data.

Therefore, large data volumes of the data table, the need for partitioning is necessary, because it can improve the efficiency of select, but also the historical data can be distinguished by the row of the archive. But the data of the small amount of data do not make this lively, because the table partition will incur unnecessary overhead on the database, in addition to performance will increase the cost and complexity of implementing object management.

Then, partitioning is so simple.

Then we will make a partition table (divided into 11 partitions), remove the mysterious veil, and then we can break down each point of the key.

Partitioning is to split a table data into sub-collections, that is, to split a data file into multiple data files, but the storage of these files can rely on a filegroup or multiple filegroups, because more than one filegroup can increase the database access concurrency, but also can be different partitions configured to different disks to improve efficiency , the recommended partition is created with the same number of filegroups.

1. Create filegroups

You can click Database properties to add a file group

T-SQL Syntax:

ALTER DATABASE < DB name > ADD filegroup < filegroup name >

---Create a DB filegroup ALTER DATABASE TESTSPLIT ADD filegroup byidgroup1alter Database testsplit add filegroup byidgroup2alter Databa SE testsplit add Filegroup byidgroup3alter Database testsplit add filegroup byidgroup4alter database Testsplit Add Filegro Up Byidgroup5alter database testsplit add filegroup byidgroup6alter Database testsplit add filegroup byidgroup7alter Datab ASE testsplit Add Filegroup byidgroup8alter Database testsplit add filegroup byidgroup9alter database Testsplit Add Filegr OUP BYIDGROUP10

2. Create a data file into a filegroup

You can click Database properties to add a file

T-SQL Syntax:

ALTER databases < database name > Add file < Data ID > to filegroup < filegroup name >--< data identity > (name: File name, Fliename: physical path file name, Size: File Initial size kb/mb/gb/tb,filegrowth: File auto increment kb/mb/gb/tb/%,maxsize: file can be increased to the maximum size kb/mb/gb/tb/unlimited)

ALTER DATABASE Testsplit Add file (name=n ' ById1 ', filename=n ' J:\Work\ database \data\byid1.ndf ', SIZE=5MB,FILEGROWTH=5MB) to Filegroup Byidgroup1alter Database testsplit Add File (name=n ' ById2 ', filename=n ' J:\Work\ databases \data\byid2.ndf ', SIZE=5MB , FILEGROWTH=5MB) to filegroup byidgroup2alter database testsplit Add File (name=n ' ById3 ', filename=n ' J:\Work\ databases \data\ Byid3.ndf ', SIZE=5MB,FILEGROWTH=5MB) to filegroup byidgroup3alter database testsplit Add File (name=n ' ById4 ', filename=n ' J:\Work\ database \data\byid4.ndf ', SIZE=5MB,FILEGROWTH=5MB) to filegroup byidgroup4alter databases testsplit Add File (name=n ' ById5 ', filename=n ' J:\Work\ database \data\byid5.ndf ', SIZE=5MB,FILEGROWTH=5MB) to filegroup byidgroup5alter database Testsplit Add File (name=n ' ById6 ', filename=n ' J:\Work\ database \data\byid6.ndf ', SIZE=5MB,FILEGROWTH=5MB) to filegroup Byidgroup6alter Database testsplit Add File (name=n ' ById7 ', filename=n ' J:\Work\ databases \data\byid7.ndf ', SIZE=5MB, FILEGROWTH=5MB) to Filegroup byidgroup7alter database testsplit Add File (name=n ' ById8 ', FilenaMe=n ' J:\Work\ database \data\byid8.ndf ', SIZE=5MB,FILEGROWTH=5MB) to filegroup byidgroup8alter databases testsplit Add File ( Name=n ' ById9 ', filename=n ' J:\Work\ database \data\byid9.ndf ', SIZE=5MB,FILEGROWTH=5MB) to filegroup Byidgroup9alter Database testsplit Add File (name=n ' ById10 ', filename=n ' J:\Work\ database \data\byid10.ndf ', SIZE=5MB,FILEGROWTH=5MB) to Filegroup BYIDGROUP10

After execution, right-click on the database to see if the file group with the file is not more out of these filegroups and files.

3. Create a partitioned table using the wizard

Right-click the table you want to partition--->> storage--->> create a partition--->> Show the wizard view--->> Next--->> Next:

Here is an example of the meaning of the selection column:

If you choose a column of type int: then your partition can be specified as 1--100w is a partition, 100w--200w is a partition ....

If you choose the Datatime type: then your partition can be specified as: 2014-01-01--2014-01-31 a partition, 2014-02-01--2014-02-28 a partition ...

According to this column data rule partitioning, then in that interval of data, when inserting the database is pointed to that partition is stored down.

I choose OrderID int type--->> next--->>

Left border right boundary: whether the threshold is divided into the previous partition or the next partition. A less than sign, a less than equals sign.

Then the next step is to finally get the partitioning function and partitioning scheme.

Use [testsplit]gobegin transaction--Creating partition Function Create PARTITION function [Bgpartitionfun] (int) as RANGE left for VALUES (N ' 100 0000 ', n ' 2000000 ', n ' 3000000 ', n ' 4000000 ', n ' 5000000 ', n ' 6000000 ', n ' 7000000 ', n ' 8000000 ', n ' 9000000 ', n ' 10000000 ')-- Creating a partition scheme create PARTITION scheme [Bgpartitionschema] as PARTITION [Bgpartitionfun] to ([PRIMARY], [ByIdGroup1], [ByIdGroup2 ], [ByIdGroup3], [BYIDGROUP4], [BYIDGROUP5], [BYIDGROUP6], [BYIDGROUP7], [BYIDGROUP8], [BYIDGROUP9], [BYIDGROUP10])-- Creating a partitioned index create CLUSTERED index [clusteredindex_on_bgpartitionschema_635342971076448165] on [dbo]. [Bigorder] (    [OrderId]) with (sort_in_tempdb = off, Ignore_dup_key = off, drop_existing = off, ONLINE = off) on [Bgpartitionschema ] ([OrderId])---delete the partition index [clusteredindex_on_bgpartitionschema_635342971076448165] on [dbo]. [Bigorder] With (ONLINE = OFF) COMMIT TRANSACTION

Executes the statements generated by the wizard above. Partition Complete:

4. Show speed.

First I inserted 10 million rows of data into the table. Divide the table by 11 partitions. One of the first 10 partitions is 100W data.

Say two words:

Visible anomalies, the number of scans and logical reads are twice times the number of non-partitioned tables, but the query speed is much faster ah. This is the magic of zoning, so believe that everything is possible in this world.

partition function, partition scheme, partition table, partition index

1. Partitioning functions

Specify the partition column (by column unique), the partitioning data range rule, the number of partitions, and then map the data to a set of partitions.

Create syntax:

create partition functions partition function name (< partition column type >) as range [Left/right] for values (boundary value per partition,....)

--Create partition function creation PARTITION function [Bgpartitionfun] (int) as RANGE left for VALUES (n ' 1000000 ', n ' 2000000 ', n ' 3000000 ', n ' 40 00000 ', n ' 5000000 ', n ' 6000000 ', n ' 7000000 ', n ' 8000000 ', n ' 9000000 ', n ' 10000000 ')

However, the partition function defines only the partitioning method, which is used specifically on the column of the table, and the table or index that is to be created is specified.

Delete syntax:

--Delete partition syntax drop partition function < partition function name >

--Delete partition functions Bgpartitionfundrop partition function Bgpartitionfun

It is important to note that only partition functions that are not applied to the partition scheme can be deleted.

2. Partitioning scheme

Specifies the filegroup that corresponds to the partition.

Create syntax:

--Creating partition scheme syntax create PARTITION scheme < partition scheme name > as partition < partition function name > [all]to (Filegroup name,....)

--create partition scheme, all partitions within a group create PARTITION SCHEME [Bgpartitionschema] as PARTITION [Bgpartitionfun] to ([ByIdGroup1], [ ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ ByIdGroup1], [ByIdGroup1])

Partition functions must be associated with partition schemes in order to be effective, however, the partition scheme specifies that the number of filegroups must match the number of partitions, even if multiple partitions reside in a filegroup.

Delete syntax:

--delete partition scheme syntax drop partition scheme< partition scheme name >

--delete partition scheme Bgpartitionschemadrop partition scheme BGPARTITIONSCHEMA1

Only the partition table is not available, or the index uses the partitioning scheme to remove it.

3. Partition table

Create syntax:

--Creating Partition Table syntax create TABLE < table name > (  < column definition >) on< partition scheme name > (partition column name)

--Creating a partitioned Table CREATE table Bigorder (   OrderId              int                  identity,   ordernum             varchar () not          null,   Orderstatus          int not                  null default 0,   orderpaystatus       int. not                  null default 0,   UserId               varchar (+) not          null,   createdate           datetime             NULL default GETDATE (),   Mark                 nvarchar (+)        null) on Bgpartitionschema (OrderId)

If you create a primary key or unique index in a table, the partitioning column must be the column.

4. Partitioned indexes

Create syntax:

--Creating a partitioned index syntax create < index category > index < index name > on < table name > (column name) on < partition scheme name > (partition by Column name)

--Creating a partitioned index create CLUSTERED index [clusteredindex_on_bgpartitionschema_635342971076448165] on [dbo]. [Bigorder] (    [OrderId]) with (sort_in_tempdb = off, Ignore_dup_key = off, drop_existing = off, ONLINE = off) on [Bgpartitionschema ] ([OrderId])

Using partitioned index queries, you can avoid conflicts that arise when multiple CPUs operate on multiple disks.

Partition indicates fine information

Here's the grammar, I will not write, I read the statement analysis it. It's very simple.

1. View the partition where the specified value of the partitioning column is located

--the query partition depends on which partition the data for column 10000014 is in select $partition. Bgpartitionfun (2000000)  --The return value is 2, indicating that this value exists in the 2nd partition

2. View the number of rows that exist for each non-empty partition in the partition table

--View the number of rows for each non-empty partition in the partition table select $partition. Bgpartitionfun (OrderID) as Partitionnum,count (*) as Recordcountfrom Bigordergroup by  $partition. Bgpartitionfun (OrderID)

3. View data records in a specified partition

---View data records in the specified partition select * from Bigorder where $partition. Bgpartitionfun (OrderID) =2

Result: Data starts at 1000001 and ends at 200W

Splitting and merging of partitions and data movement

1. Splitting partitions

Add a boundary value to the partition function to change one partition to 2.

--partition split ALTER PARTITION function Bgpartitionfun () split range (N ' 1500000 ')  --splits the second partition into 2 partitions

Note: If the partition function has already specified a partitioning scheme, the number of partitions needs to be consistent with the number of filegroups specified in the partition scheme.

2. Merging partitions

Instead of splitting a partition, remove one of the boundary values.

--merge partition ALTER PARTITION function Bgpartitionfun () Merge range (N ' 1500000 ')  --merge the second third partition

3. Data movement in a partition

You might encounter the need to copy plain table data to a partitioned table, or copy data from a partitioned table to a normal table.

Then move the data to both tables, you must meet the following requirements.

• Same number of fields, same field in corresponding position
• Fields of the same location should have the same properties as the same type.
• Two tables in a filegroup

1. Specifying a filegroup when creating a table

--Create TABLE < table name > (  < column definition >) on < file group name >

2. Copying data from a partitioned table to a normal table

--Copy the first partition data from the Bigorder partition table to the normal table ALTER TABLE Bigorder switch partition 1 to < normal table name >

3. Copying data from a generic standard to a partitioned table

It is important to note that the indexes in the partitioned table are removed first, even if there are indexes in the normal table that are identical to the partition table.

Partitioned views

Partitioned views are the same tables with field constraints, with different constraints, for example, the first table has an ID constraint of 0--100w, and the second table is 1.01 million to 2 million .... In turn.

After creating a series of tables, the union all joins together to create a view, which forms a partitioned viewport.

Very simple, here I mainly say partition table, do not say partition view.

View database partition information

SELECT object_name (p.object_id) as ObjectName, i.name as IndexName, p.index_id A S IndexID, ds.name as Partitionscheme, P.partition_number as PartitionNumber, FG.          Name as FileGroupName, Prv_left.value as Lowerboundaryvalue, Prv_right.value    As Upperboundaryvalue, case pf.boundary_value_on_right if 1 then "right" ELSE ' left ' END As Range, p.rows as Rowsfrom sys.partitions as Pjoin sys.indexes as I on I. object_id = p.object_id and i.index_id = P.index_idjoin sys.data_spaces as DS on Ds.data_space_i D = I.data_space_idjoin Sys.partition_schemes as PS on ps.data_space_id = Ds.data_space_idjoin Sys.partitio N_functions as PF on pf.function_id = Ps.function_idjoin sys.destination_data_spaces as DDS2 on DDS2 . partition_scheme_id = Ps.data_space_id and dds2.destination_id = P.partition_numberjoin sys.filegroups as FG on Fg.da ta_space_id = Dds2.data_space_idleft JOIN sys.partition_range_values as prv_left on ps.function_id = Prv_left.functio n_id and prv_left.boundary_id = P.partition_number-1left JOIN sys.partition_range_values as Prv_right on Ps.fu nction_id = prv_right.function_id and prv_right.boundary_id = P.partition_number WHERE objectproperty (p.object_i D, ' ismsshipped ') = 0UNION allselect object_name (p.object_id) as ObjectName, i.name as I Ndexname, p.index_id as IndexID, NULL as Partitionscheme, P.partiti                        On_number as PartitionNumber, fg.name as FileGroupName, NULL As Lowerboundaryvalue, null as Upperboundaryvalue, NULL as Boun      Dary, P.rows                As Rowsfrom sys.partitions as Pjoin sys.indexes as I on i.object_id = p.object_id and           i.index_id = P.index_idjoin sys.data_spaces as ds on ds.data_space_id = I.data_space_idjoin sys.filegroups      As FG on fg.data_space_id = I.data_space_idwhere objectproperty (p.object_id, ' ismsshipped ') = 0ORDER by ObjectName, IndexID, PartitionNumber
From: http://www.cnblogs.com/knowledgesea/p/3696912.html

SQL Server Table Partitioning