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

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:

 create  PARTITION function  Partfunsale (datetime )  RANGE right  for  values  ( "  ",  Span style= "Color:rgb (255, 0, 0); >20110101   ",  Span style= "Color:rgb (255, 0, 0); >20120101   ",  Span style= "Color:rgb (255, 0, 0); >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

CREATE FUNCTION partfunsale (datetime)    as right forVALUES ('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:

ALTER FUNCTION Partfunsale ()      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:

--count the total number of records in all partitioned tablesSelect$PARTITION. Partfunsale (Saletime) asPartition number,Count(ID) asNumber of records fromSaleGroup  by$PARTITION. Partfunsale (saletime)--the original partitioning function was to place data before 2010-1-1 in the 1th partition table, with data from 2010-1-1 to 2011-1-1 placed in the 2nd partition table--It is now necessary to place data before 2011-1-1 in the 1th partition table, which is the merging of data from the 1th partition table and the 2nd partition table--Modifying a partition functionALTERPARTITIONFUNCTIONPartfunsale () MERGE RANGE ('20100101')  --count the total number of records in all partitioned tablesSelect$PARTITION. Partfunsale (Saletime) asPartition number,Count(ID) asNumber of records fromSaleGroup  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