Data Partitioning - Pro SQL Server Internals - page 316

Now let's rebuild the clustered index, moving the data to the FG2 filegroup. The code for doing this is shown in
Listing 15-13.
Listing 15-13. Rebuilding the index by moving data to a different filegroup
create unique clustered index IDX_RegularTable_OrderDate_OrderId
on dbo.RegularTable(OrderDate, OrderId)
with (drop_existing=on, online=on)
on [FG2]
Now if you run the query from Listing 15-12 again, you will see the results shown in Figure 15-9 . As you can see,
the index rebuild moved IN_ROW_DATA and ROW_OVERFLOW_DATA allocation units to the new filegroup, keeping
LOB_DATA intact.
Figure 15-9. Allocation units placement after index rebuild
Fortunately, there is a workaround available. You can move LOB_DATA allocation units to another filegroup by
performing an online index rebuild using a partition scheme rather than a filegroup as the destination.
Listing 15-14 shows such an approach. As a first step, you need to create a partition function with one boundary
value and two partitions in a way that leaves one partition empty. After that, you need to create a partition scheme
using a destination filegroup for both partitions and perform an index rebuild into this partition scheme. Finally, you
need to merge both partitions by altering the partition function. This is quick metadata operation because one of the
partitions is empty.
Listing 15-14. Rebuilding an index in a partition scheme
create partition function pfRegularTable(date)
as range right for values ('2100-01-01');
create partition scheme psRegularTable
as partition pfRegularTable
all to ([FG2]);
create unique clustered index IDX_RegularTable_OrderDate_OrderId
on dbo.RegularTable(OrderDate, OrderId)
with (drop_existing=on, online=on)
on psRegularTable(OrderDate);
alter partition function pfRegularTable()
merge range('2100-01-01');
Figure 15-10 shows the allocation units placement after the index rebuild.