partition table in SQL Server 2005 (v): adding a partition

The so-called world events, a long time will be together, a long time will be divided, for the partition table is the same. Before we described how to delete (merge) a partition in a partitioned table, let's look at how to add a partition to a partitioned table.

Adding a partition to a partitioned table is often the case. For example, when the database was originally designed, only 3 years of data were expected to be stored, but what about the 4th day? In this case, we can add a partition to the partition table and let it put the new data in the new partition. For example, when initially designed, a partition is used to hold data for one year, and the results are used to find that there is too much data in one year to divide the data in a partition into two partitions for storage.

In this case, you must add a partition to the partition table.

Of course, we can also add a partition using the method of modifying the partition function, but when we modify the partition function, we have to pay attention to another problem-the partitioning scheme. Why pay attention to the partitioning scheme? Let's look back at how the partition function and partitioning scheme are defined earlier, as shown in the following code:

[C-sharp]View PlainCopy

1. --Add partition function
2. CREATE PARTITION FUNCTION Partfunsale (datetime)
3. As RANGE right for VALUES (' 20100101 ',' 20110101 ',' 20120101 ',' 20130101 ')
4. --Add partition scheme
5. CREATE PARTITION SCHEME Partschsale
6. As PARTITION Partfunsale
7. To (Sale2009, Sale2010, Sale2011, Sale2012, Sale2013)

As can be seen from the above code, the partition function defines the data boundary used for the partition, and the partition function specifies that the data that conforms to 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 score area function. As in the previous example, the number of partitions specified in the partition function is 4, and the number of filegroups specified in the partition scheme is 5.

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

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

There is no mistake in thinking that you want to add a partition to a partitioned table, which can be done in the following two steps:

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

2, modify the partition function.

When you specify an available filegroup for a partition scheme, the partition scheme does not immediately use the filegroup, except that the filegroup is first reserved, and then the partitioning scheme is used after the partition function has been modified (do not forget that if the partition function is not changed, the number of filegroups in the partition scheme cannot be changed).

The code for specifying an available filegroup for the partitioning scheme is as follows:

[C-sharp]View PlainCopy

1. ALTER PARTITION SCHEME Partschsale
2. NEXT used [Sale2010]

which

1. Alter PARTITION scheme means to modify the partition scheme

2. Partschsale is the name of the partition scheme

3. Next used means the next filegroup to use

4, [Sale2010] is the file group name

After adding the next available filegroup for the partitioning scheme, the partitioning scheme does not immediately use the filegroup, and we can verify it by looking at the source code of the partitioning scheme. To see this: in SQL Server Management Studio, select the database----------Save the partition scheme, right-click the partition scheme name, and in the pop-up menu choose "Write partition Scheme script to"-->create- The New Query Editor window, as shown in:

After adding the next available filegroup for the partitioning scheme, we can modify the partition function, using the code as follows:

[C-sharp]View PlainCopy

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

which

1. alter PARTITION function means to modify the partition functions

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

3, split RANGE means the dividing line

4, ' 20100101 ' is the boundary value used 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:

[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. SPLIT 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 result of the above code is as follows:

As can be seen in the partition table has been added a partition, we can also see the source code of the partition scheme, as shown, this time the partition scheme also automatically added a filegroup.

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

partition table in SQL Server 2005 (v): adding a partition