partition table in SQL Server 2005 (iv): Delete (merge) a partition

In the previous section we described how to create and use a partitioned table, with an example of placing data from different years in different physical partitions. The specific partitioning method is:

1th Small table: Data prior to 2010-1-1 (not including 2010-1-1).

2nd Small table: Data from 2010-1-1 (including 2010-1-1) to 2010-12-31.

3rd Small table: Data from 2011-1-1 (including 2011-1-1) to 2011-12-31.

4th Small table: Data from 2012-1-1 (including 2012-1-1) to 2012-12-31.

5th small table: Data after 2013-1-1 (including 2013-1-1).

The code for the partition function is as follows:

[C-sharp]View Plaincopy

1. CREATE PARTITION FUNCTION Partfunsale (datetime) as RANGE right for VALUES (' 20100101 ',' 20110101 ',' 20120101 ' ,' 20130101 ')

Assuming that we have created the partitioned table, we found that before 2010 there was not much data, and it was possible to merge them with the 2010 data and put them in the same partition, that is to say, the specific partitioning method should read:

1th Small table: Data prior to 2011-1-1 (not including 2011-1-1).

2nd Small table: Data from 2011-1-1 (including 2011-1-1) to 2011-12-31.

3rd Small table: Data from 2012-1-1 (including 2012-1-1) to 2012-12-31.

4th Small table: Data after 2013-1-1 (including 2013-1-1).

Because the requirements above change the condition of the data partition, we have to modify the partition function, because the function of the partition function is to tell SQL Server how to store the data. As soon as the partition function is modified, SQL Server automatically assigns the data and stores the data in the manner specified by the new partition function.

Let's say we haven't created an over-the-block table yet, to meet the above criteria, we have to write the SQL statement that creates the partition function as follows

[C-sharp]View Plaincopy

1. CREATE PARTITION FUNCTION Partfunsale (datetime)
2. As RANGE right for VALUES (' 20110101 ',' 20120101 ',' 20130101 ')

Compare a new partition function with an old partition function to see what difference they have?

Indeed, it is easy to see that the old partitioning function has a more dividing value-that is, ' 20100101 '. Then, modifying the old partition function is actually removing the cutoff value. Simply put, deleting (merging) a partition is, in fact, removing the extra cutoff value in the partition function.

Remove the cutoff value in the partition function, that is, the method of modifying the partition function is as follows:

[C-sharp]View Plaincopy

1. ALTER PARTITION FUNCTION Partfunsale ()
2. MERGE RANGE (' 20100101 ')

which

1. alter PARTITION function means to modify the partition functions

2, Partfunsale () is the name of the partition function

3. Merge range means the merge boundary. In fact, merging boundaries and deleting boundary values is a meaning.

We can change the partition function when the total number of records in each physical partition, after modifying the partition, and then count the total number of records in each physical partition, look at the changes in the data after modifying the partition function, the code is as follows:

[C-sharp]View Plaincopy

2. --Count the total number of records in all partition tables
4. Select $PARTITION. Partfunsale (saletime) as partition number, COUNT (ID) as record number from the Sale group by $PARTITION. Partfunsale ( Saletime)
6. --The original partition function was to place the data before 2010-1-1 in the 1th partition table, and the data between 2010-1-1 and 2011-1-1 was placed in the 2nd partition table
8. --You now need to put the data from 2011-1-1 to the 1th partition table, that is, merge the data from the 1th partition table and the 2nd partition table
10. --Modifying the partition function
12. ALTER PARTITION FUNCTION Partfunsale ()
14. MERGE RANGE (' 20100101 ')
16. --Count the total number of records in all partition tables
18. Select $PARTITION. Partfunsale (saletime) as partition number, COUNT (ID) as record number from the Sale group by $PARTITION. Partfunsale ( Saletime)

The results of the operation are as follows:

Now there is the question of where the data is stored after merging the data by modifying the partition function. Before the changes, the data were stored in the filegroup Sale2009 and Sale2010, where did the data go after the modifications?

In fact, after modifying the partition function, SQL Server automatically modifies the partitioning scheme, placing the data in the same physical partition in the two physical partitions. You can see where the data is stored by looking at the partitioning scheme.

The way to view the partitioning scheme is: In SQL Server Management Studio, select the database---------Save the partition scheme, right-click the partition scheme name, and select "Write partition scheme script as"-->create to "in the popup menu New Query Editor window

You can then see the code in the New Query Editor window.

As you can see, the partitioning scheme merges the data from the original Sale2010 filegroup into the SALE2009 filegroup.

Original is not easy, reproduced please indicate the source. Http://blog.csdn.net/smallfools/archive/2009/12/04/4937878.aspx