Meaning of SQL Server 2008 table Partitioning

Keep reading this document.

Http://www.360doc.com/content/16/0104/11/22743342_525336297.shtml

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

Category: MSSQL Database Tags: table partition, partition table good text to the top of my collection this article

the? Previous: Optimization analysis of SQL statements
? Next post: SQL Server cursor posted @2014-04-29 09:01 Zhang Long-hao read (70599) Comment (27) Edit Collection

List of commentsReply Reference#1楼2014-04-29 09:23 Sam XiaoTable partitioning, divided into horizontal and vertical partitions.

You're just a horizontal partition.Reply Reference#2楼 [Landlord] 2014-04-29 09:29 Zhang Long hao @ Sam Xiao
RightReply Reference#3楼2014-04-29 11:41 Passerby awas educated.Reply Reference#4楼2014-04-29 11:42 Xiaoxiang HermitA good summary. GoodReply Reference#5楼2014-04-29 12:58 RichiezhangIt's a good summary, a collection.Reply Reference#6楼2014-04-29 18:23 Sentosa WaveIf 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.
As you can see, there are 12 partitions in 1 years, and if it grows over time, how do you partition it? Also, can you partition a table that already has a large amount of data stored? What is the reasonable time-type partitioning scheme?Reply Reference#7楼 [Landlord] 2014-04-29 19:38 Zhang Long hao @ Sentosa Wave
That's just an example, you can divide a year's data into one area, or you can divide one day's data into a single area, depending on the amount of data and your needs.Reply Reference#8楼2014-04-30 17:04 Richard__leeWhy do I test the data and the landlord is not the same? If you use a clustered index as the partition of the field, the basic can not find the performance gap between the two, if it is useless to reflect a little.Reply Reference#9楼2014-04-30 17:08 Segment Hejiang@ Richard__lee

This is related to the amount of total data in the library and how much data you want to extract.Reply Reference#10楼2014-05-01 13:05 Richard__lee@ Hope River
Is the landlord so tens of millions of each division to take 10,000Reply Reference#11楼 [Landlord] 2014-05-01 14:51 Zhang Long hao @ Richard__lee
Too much data to check.Reply Reference#12楼2014-11-27 11:58 Lucika.zhHard.Reply Reference#13楼2015-04-29 09:32 qly2046I perform "alter PARTITION function [Bgpartitionperson] ()
Split Range (N ' 2012-01-01t00:00:00 ')--split the second partition into 2 partitions ", error" Warning: Partition scheme ' Bgpartitionperson ' does not have any next-use filegroups. The partition scheme has not changed. "Always do not understand what reason?"Reply Reference#14楼2015-05-20 10:27 a knowledgeableLandlord hard! Thank you for sharing!Reply Reference#15楼2015-07-20 19:39 Cao Co-liangBig Data Ah!Reply Reference#16楼2015-10-29 11:35 NewkoinfinitiI have two tables with the same structure, but the table names are different, in the same database. The first table is called the user table, after the creation I will fill in the 10w data, then the table select into the User_new, and then according to the landlord's way to the User_new table to create partitions. Later, when I queried, I found that the query speed of two tables was almost the same. I looked at the partition again and found:
SELECT * FROM [TestDB]. [dbo]. [User_new] where $partition. Userpartfounction (ID) =2
And
SELECT * FROM [TestDB]. [dbo]. [User_new] where $partition. Userpartfounction (ID) =2
Can find the data, is not that I created this table partition when the user and user_new have created the same table partition AH?Reply Reference#17楼2015-10-29 11:36 NewkoinfinitiSELECT * FROM [TestDB]. [dbo]. [User_new] where $partition. Userpartfounction (ID) =2
And
SELECT * FROM [TestDB]. [dbo]. [user] where $partition. Userpartfounction (ID) =2
Can find the data, is not that I created this table partition when the user and user_new have created the same table partition AH?Reply Reference#18楼2015-11-03 10:40 ShukeIt's hard.Reply Reference#19楼2016-01-08 18:01 i love cabbage +1Excuse me, does this partition have to be a filegroup for a data file? If you have multiple data files in a filegroup, how do you specify which file the data is stored in?Reply Reference#20楼2016-05-11 11:54 JigerIs there anyone else verifying the efficiency of the partitioned table?

My understanding is that partitioning is more focused on database management, such as backing up data by partition, restoring, drop, or delete. But for queries that have a limited productivity boost (compared to indexed, high-quality query), you see another post that way.Reply Reference#21楼2016-07-28 11:26 AnlodIn accordance with the post of the landlord, the control did a test, found that the last two paragraphs of SQL query time basically consistent, check IO CPU read log, found that the partition table has no partition table efficiency, data volume is 10 million, logic and landlord hair consistent, but did not achieve the effect, which is why?Reply Reference#22楼2016-09-13 13:19 future. Learn!!Reply Reference#23楼2016-09-21 09:35 BiindThanks for sharing.Reply Reference#24楼2016-11-16 10:22 Spirit Rain WanderingIt's a good summary, a collection.Reply Reference#25楼2016-12-28 20:12 TangLandlord, I have a few questions, I hope you can help me answer, thank you!
1. If I have 10 tables with a ROWID field, and I want these tables to be partitioned using this field in the same scenario, can I share a partitionscheme and a partitionfunction? Or do I need to create 10 similar partitionscheme and 10 partitionfunction to give them a separate use?
2. Section III: "3. create partition Table Using Wizard" The last two steps are "create Partition index", "Delete Partition index", why do you create partition index and delete this partition index immediately?
3. If a table already has a clustered index, I do not want to use the clustered index column to partition, you can use the other columns to partition and still keep the original column clustered index it?
4. Does the index expire when I delete a partition?

Thank you ~Reply Reference#26楼2017-01-09 09:24 2604529MarkReply Reference#27楼2017-08-18 14:45 Allen_changLandlord, ask the partition to improve the performance of the need to change the code? such as C # code

Meaning of SQL Server 2008 table Partitioning