SQL Server Partitioning detailed examples

In the daily work, we will encounter the following situation, a table daily growth of tens of thousands of levels, and the query data is usually in this month or this year, the previous data is occasionally used, but the efficiency of query and insertion is getting slower, using database partitioning will help solve this problem. On the theoretical knowledge of partitioning online a lot I'm not a liability here, I start with a practical example of how to partition a normal table that has been running for a long time.

Go back to the catalogue and ask questions

Need to solve the problem: there is a large data table data, our usual query is in a quarter. We need to have data for previous years in the file group in different years, the data of the year is divided into 4 quarters, if the new year, the previous 4 quarters of the consolidation into a year, the New Year and the 4 quarterly division.

Back to the catalog to solve the problem

Okay, we're going to start with a step-by-point solution.

Setting up a simulation environment

1. First build the database, and create the table.

Create a new database and create a new test table. The data file is placed in an easy-to-find folder where the partition files are placed.

2. Create the simulation data.

I used a C # program to simulate inserting some data from 2015-9-1 to 2017-4-1 per day. The properties of this table are as follows, the filegroup primary, not partitioned.

Create a partition file

New 5 filegroups, corresponding to 5 database files, Y2015 Store 2015 years of data, Q1,q2,q3,q4 storage for 4 quarters of data, here we put the files in the same folder, if the conditions allow, on different disk will increase read and write efficiency.

Create a partition function

Partition function range has a distinction between left and right
Left is the first partition less than or equal to the boundary, the second partition is greater than
Right is the first partition less than the boundary, the second partition is greater than or equal to

CREATEPARTITIONfunction [Partitionfunc] (datetime) as range  Right for values (n ' 2016-01-01t00:00:00 ', n ' 2016-04-01t00:00:00 ', n ' 2016-07-01t00:00:00 ', n ' 2016-10-01t00:00:00 ', n ' 2017-01-01t00:00:00 ')      

Establish a partitioning scheme

This partition function will be divided into 6 filegroups

CREATE PARTITION SCHEME [PartitionScheme] AS PARTITION [PartitionFunc] TO ([Y2015], [Q1],[Q2],[Q3],[Q4],[PRIMARY])

The partitioning functions and partitioning schemes are set up as follows:

To create a partitioned index to complete a partition

The partition index must be a clustered index, and we will automatically set the ID to a clustered index using the primary key setting in SQL. Here we need to change the original primary key to a clustered index, in the establishment of a partitioned index.

CREATE CLUSTERED INDEX [ClusteredIndex_CreateDate] ON [dbo].[SchemTest] ( [CreateDate] )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartitionScheme]([CreateDate])

So the table partition is complete.

Querying data in a partition

We can check the data in different partitions, the following statements:

select $PARTITION.PartitionFunc(CreateDate) as 分区编号,count(ID) as 记录数 from SchemTest group by $PARTITION.PartitionFunc(CreateDate) 

select * from SchemTest where $PARTITION.PartitionFunc(CreateDate)=1 

This queries all data for 2015 years.

Partition additions and merges

Now 2015 data in 2015 filegroup, 2016 data in 4 quarter of filegroup, 2017 data in primary filegroup, now to put 2016 data in the new 2016 filegroups, 4 quarters of the filegroups put 2017 data, 2018 years after the primary was released.

1. Create a new 2016 filegroup

2. Partition merging
Merge all the quarterly filegroups first so that 2017 data is in the 2015 filegroup before

AlterPARTITIONFUNCTION Partitionfunc ()MERGERANGE (N' 2016-01-01t00:00:00 ');AlterPARTITIONFUNCTION Partitionfunc ()MERGERANGE (N 2016-04-01t00:00:00 '); alter PARTITION function partitionfunc () merge range (N  ' 2016-07-01t00:00:00 '); alter PARTITION function partitionfunc () merge range (N  ' 2016-10-01t00:00:00 ');         

You can view the Create SQL statement on the partitioning scheme, when the partitioning scheme has changed to:

CREATE PARTITION SCHEME [PartitionScheme] AS PARTITION [PartitionFunc] TO ([Y2015], [PRIMARY])

3. New Partition

First put 2016 data in the Y2016 filegroup

--选择文件组
ALTER PARTITION SCHEME PartitionScheme NEXT USED [Y2016] ; --修改分区函数 ALTER PARTITION FUNCTION PartitionFunc() SPLIT RANGE (N‘2016-01-01T00:00:00.000‘) ;

In the same vein, the 2017 figures were placed in the 2017 quarter.

AlterPARTITIONFUNCTION Partitionfunc ()MERGERANGE (N' 2017-01-01t00:00:00 ');AlterPARTITION SCHEME PartitionschemeNEXT used [Q1];AlterPARTITIONFUNCTION Partitionfunc ()SPLITRANGE (N' 2017-01-01t00:00:00.000 ');AlterPARTITION SCHEME PartitionschemeNEXT used [Q2];AlterPARTITIONFUNCTION Partitionfunc ()SPLITRANGE (N' 2017-04-01t00:00:00.000 ');AlterPARTITION SCHEME PartitionschemeNEXT used [Q3];AlterPARTITIONFUNCTION Partitionfunc ()SPLITRANGE (N' 2017-07-01t00:00:00.000 ');AlterPARTITION SCHEME PartitionschemeNEXT used [Q4];AlterPARTITIONFUNCTION Partitionfunc ()split RANGE (nalter PARTITION SCHEME Partitionscheme next used [primary]; alter PARTITION function partitionfunc () split range (N  ' 2018-01-01t00:00:00.000 ');         

Now look at the CREATE statement for the partition function and partition scheme as follows:

CREATEPARTITION SCHEME [Partitionscheme]AsPARTITION [Partitionfunc]to ([Y2015], [Y2016], [Q1], [Q2], [Q3], [Q4], [PRIMARY])CREATEPARTITIONFUNCTION [Partitionfunc] (datetime)Asrange RIGHT for values (n ' 2016-01-01t00:00:00.000 ', n ' 2017-01-01t00:00:00.000 ', n ' 2017-04-01t00:00:00.000 ', n ' 2017-07-01t00:00:00.000 ', n ' 2017-10-01t00:00:00.000 ', n ' 2018-01-01t00:00:00.000 ')   

The partition records are as follows:

If the partition changes relatively large does not recommend the method of merging and deletion, because error prone, if the 12 months recommended as follows, the partition table is converted to a normal table, and then the normal table partition.

Converting a partitioned table into a normal table

1. Deleting a partitioned index

After deleting the partition index, there is no programming plain table

2. In the original Partition index field, create a normal index

CREATE CLUSTERED INDEX [IX_SchemTest]  ON SchemTest(CreateDate) ON [Primary]

SQL Server Partitioning detailed examples