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

This article introduces the knowledge of "how to add a partition to SQL Server". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

-- add partition function

CREATE PARTITION FUNCTION partfunSale (datetime)

AS RANGE RIGHT FOR VALUES ('20100101, 20110101, 20120101, 20130101)

-- add a partition scheme

CREATE PARTITION SCHEME partschSale

AS PARTITION partfunSale

TO (Sale2009, Sale2010, Sale2011, Sale2012, Sale2013)

As you can see from the above code, the partition function defines the data boundary for the partition, while the partition function specifies that the data that meets the partition boundary is stored in the filegroup. Therefore, the number of filegroups specified in the partitioning scheme should be 1 greater than the number of boundaries specified in the partitioning function. As in the example above, if the number of boundaries specified in the partition function is 4, then the number of filegroups specified in the partition scheme is 5.

If we increase the number of boundaries in the partition function by one, then the number of filegroups in the partition scheme will be increased by one accordingly. Therefore, we cannot simply add a partition to the partition table by modifying the partition function.

So, what should we do? Do you want to add a filegroup to the partition scheme first?

There is nothing wrong with this idea, and you can add a partition to the partition table through the following two steps:

1. Specify a filegroup that can be used for the partition scheme.

2. Modify the partition function.

When specifying an available filegroup for the partition scheme, the partition scheme does not use the filegroup immediately, but only sets it aside until the partition function is modified (don't forget, if the partition function does not change, the number of filegroups in the partition scheme cannot be changed).

The code to specify an available filegroup for the partition scheme is as follows:

ALTER PARTITION SCHEME partschSale

NEXT USED [Sale2010]

Where:

1. ALTER PARTITION SCHEME means to modify the partition scheme

2. PartschSale is the partition scheme name

3. NEXT USED means the next available filegroup

4. [Sale2010] is the filegroup name

After adding the next available filegroup to the partitioning scheme, the partitioning scheme does not use this filegroup immediately, which can be confirmed by looking at the source code of the partitioning scheme. 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 from the pop-up menu, as shown below:

After adding the next available filegroup to the partition scheme, we can modify the partition function by using the following code:

ALTER PARTITION FUNCTION partfunSale ()

SPLIT RANGE ('20100101')

Where:

1. ALTER PARTITION FUNCTION means to modify the partition function

2. PartfunSale () is the partition function name

3. SPLIT RANGE means dividing boundaries

4. '20100101' is the boundary value for segmentation

Of course, we can count the data records of each physical partition before and after modifying the partition function, as shown in the following code:

-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 ()

SPLIT 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 of the above code is shown in the following figure:

As you can see from the above figure, a partition has been added to the partition table, and we can also look at the source code of the partition scheme again, as shown in the following figure, when the partition scheme automatically adds a filegroup.

This is the end of the content of "how to add a partition to SQL Server". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.