Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to delete or merge a partition by SQL Server". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how SQL Server deletes or merges a partition.

Earlier we showed how to create and use a partition table and gave an example of putting data from different years in different physical partition tables. The specific zoning methods are:

The first small table: data prior to 2010-1-1 (excluding 2010-1-1).

The second small table: data between 2010-1-1 (including 2010-1-1) and 2010-12-31.

The third small table: data between 2011-1-1 (including 2011-1-1) and 2011-12-31.

The fourth small table: data between 2012-1-1 (including 2012-1-1) and 2012-12-31.

The fifth 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) AS RANGE RIGHT FOR VALUES ('20100101' 20110101')

Suppose that after creating the partition table, we find that there is not much data before 2010, so we can merge them with the 2010 data and put them in the same partition, that is, the specific partitioning method can be changed to:

The first small table: data prior to 2011-1-1 (excluding 2011-1-1).

The second small table: data between 2011-1-1 (including 2011-1-1) and 2011-12-31.

The third small table: data between 2012-1-1 (including 2012-1-1) and 2012-12-31.

The fourth small table: data after 2013-1-1 (including 2013-1-1).

Because the above requirements change the conditions of data partitioning, we have to modify the partitioning function, because the purpose of the partitioning function is to tell SQL Server how to store the data. Whenever the partition function is modified, SQL Server automatically reassigns the data to store the data in the way specified by the new partition function.

Assuming that we haven't created the overextent table yet, to meet the above conditions, we have to write the SQL statement that creates the partition function in the following code

CREATE PARTITION FUNCTION partfunSale (datetime)

AS RANGE RIGHT FOR VALUES ('20110101')

Compare a new partition function with the old partition function and see the difference between them.

Indeed, we can easily find that there is an extra cut-off value in the old partition function-- that is, '20100101'. So, to modify the old partition function is actually to delete the boundary value. To put it simply, to delete (merge) a partition is actually to delete the extra demarcation value in the partition function.

The way to delete the demarcation value in the partition function, that is, to modify the partition function, is as follows:

ALTER PARTITION FUNCTION partfunSale ()

MERGE RANGE ('20100101')

Where:

1. ALTER PARTITION FUNCTION means to modify the partition function

2. PartfunSale () is the partition function name

3. MERGE RANGE means to merge boundaries. In fact, merging boundaries and deleting demarcation values have the same meaning.

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

-Statistics of the total number of records in all partition tables

Select $PARTITION.partfunSale (SaleTime) as partition number, count (id) as records from Sale group by $PARTITION.partfunSale (SaleTime)

The original partition function was to put data prior to 2010-1-1 in the first partition table and data between 2010-1-1 and 2011-1-1 in the second partition table.

-- now you need to put all the data before 2011-1-1 in the first partition table, that is, merge the data in the first partition table and the second partition table.

-- modify partition function

ALTER PARTITION FUNCTION partfunSale ()

MERGE RANGE ('20100101')

-Statistics of the total number of records in all partition tables

Select $PARTITION.partfunSale (SaleTime) as partition number, count (id) as records from Sale group by $PARTITION.partfunSale (SaleTime)

The running result is shown in the following figure:

Now there is another question, that is, after merging the data by modifying the partition function, where is the data stored? Before modification, the data is stored in filegroup Sale2009 and Sale2010, respectively. After modification, where does the data go?

In fact, after modifying the partition function, SQL Server automatically changes the partition scheme to put the data in the two physical partitions in the same physical partition. You can view the exact location of the data by looking at the partition scheme.

The way to view the partition scheme is: in SQL Server Management Studio, select Database-> Storage-> Partition Scheme, right-click the partition scheme name, and select "script partition scheme as"-- > CREATE to-- > New query Editor window in the pop-up menu.

Then you can see the following code in the new query Editor window.

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

At this point, I believe you have a deeper understanding of "how SQL Server deletes or merges a partition". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.