Database Reference
In-Depth Information
Table 15-5.
RANGE RIGHT
partition function and
MERGE
operations
FG1
FG2
FG3
FG4
Original
{min..9}
{10..19}
{20..29}
{30..max}
MERGE RANGE(10)
{min.. 19}
{20..29}
{30..max}
MERGE RANGE(20)
{min..9}
{10..29}
{30..max}
MERGE RANGE(30)
{min..9}
{10..19}
{20..max}
When you move a partition to a different filegroup, you should choose a boundary value to
SPLIT
and
MERGE
the
partition function based on that behavior. For example, if you want to move a partition that stores May 2014 data in
the
OrdersPT
table from the
FASTSTORAGE
to the
FG2014
filegroup, you need to
MERGE
and
SPLIT
a boundary value of
2014-05-01. The partition function is defined as
RANGE RIGHT
and, as a result, the
MERGE
operation moves May 2014
data to the partition containing the April 2014 data, which resides on the
FG2014
filegroup. Afterward, the
SPLIT
operation would move the May 2014 data to the filegroup you specified as
NEXT USED
by altering partition scheme.
You can see the code to accomplish this in Listing 15-17. As a reminder, the
OrdersPT
table was created in
Listing 15-1.
Listing 15-17.
Moving data for a single partition
-- Moving May 2014 partition data to April 2014 filegroup
alter partition function pfOrders()
merge range ('2014-05-01');
-- Marking that next used filegroup
alter partition scheme psOrders
next used [FG2014];
-- Creating new partition for May 2014 moving it to FG2014
alter partition function pfOrders()
split range ('2014-05-01');
Even though the code is very simple, there are a couple problems with such an approach. First, the data is moved
twice when you
MERGE
and
SPLIT
a partition function. Another problem is that SQL Server acquires and holds schema
modification (SCH-M) lock for the duration of the data movement, which prevents other sessions from accessing the table.
There is no easy workaround for the problem of keeping the table online during data movement. One of the
options, shown in Listing 15-18, is to rebuild the indexes using a different partition scheme. Even though this
operation can be performed online, it introduces a huge I/O and transaction log overhead because you are rebuilding
indexes in the entire table rather than moving a single partition. Moreover, this operation will not work online in SQL
Server 2005-2008R2 if the table has LOB columns.
Listing 15-18.
Moving data for a single partition
create partition scheme psOrders2
as partition pfOrders
to (
FG2012,FG2012,FG2012,FG2012,FG2012,FG2012,FG2012,FG2012
,FG2012,FG2012,FG2012,FG2012,FG2013,FG2013,FG2013,FG2013
,FG2013,FG2013,FG2013,FG2013,FG2013,FG2013,FG2013,FG2013
,FG2014,FG2014,FG2014,FG2014,FASTSTORAGE,FASTSTORAGE
);
