Data Partitioning - Pro SQL Server Internals - page 317

Figure 15-10. Allocation units placement after rebuilding the index in the partition scheme

Obviously, this method requires the Enterprise Edition of SQL Server. It also would require SQL Server 2012 or
above to work as an online operation because of the LOB columns involved.
Without the Enterprise Edition of SQL Server, your only option for moving LOB_DATA allocation units is to create a
new table in the destination filegroup and copy the data to it from the original table.

Moving Partitions Between Filegroups
You can move a single partition from a partitioned table to another filegroup by altering the partition scheme and
function. Altering the partition scheme marks the filegroup in which the newly created partition must be placed.
Splitting and merging the partition function triggers the data movement.
The way that data is moved between partitions during RANGE SPLIT and RANGE MERGE operations depends on the
RANGE LEFT and RANGE RIGHT parameters of the partition function. Let's look at an example and assume that you have
a database with four filegroups: FG1 , FG2 , FG3 , and FG4 . You have a partition function in the database that uses RANGE
LEFT values, as shown in Listing 15-15.

Listing 15-15. RANGE LEFT partition function

create partition function pfLeft(int)
as range left for values (10,20);

create partition scheme psLeft
as partition pfLeft
to ([FG1],[FG2],[FG3]);

alter partition scheme psLeft
next used [FG4];

In a RANGE LEFT partition function, the boundary values represent the highest value in a partition. When you split
a RANGE LEFT partition, the new partition with the highest new boundary value is moved to the NEXT USED filegroup.
Table 15-2 shows a partition and filegroup layout for the various SPLIT operations.

Table 15-2. RANGE LEFT partition function and SPLIT operations

FG1 FG2 FG3 FG4

Original {min..10} {11..20} {21..max}

SPLIT RANGE(0) {1..10} {11..20} {21..max} {min..0}

SPLIT RANGE(15) {min..10} {16..20} {21..max} {11..15}

SPLIT RANGE(30) {min..10} {11..20} {31..max} {21..30}