Data Partitioning - Pro SQL Server Internals - page 320

create unique clustered index IDX_OrdersPT_OrderDate_OrderId
on dbo.OrdersPT(OrderDate, OrderId)
with
(
data_compression = page on partitions(1 to 28),
data_compression = none on partitions(29 to 31),
drop_existing = on, online = on
)
on psOrders2(OrderDate);
create nonclustered index IDX_OrdersPT_CustomerId
on dbo.OrdersPT(CustomerId)
with
(
data_compression = page on partitions(1 to 28),
data_compression = none on partitions(29 to 31),
drop_existing = on, online = on
)
on psOrders2(OrderDate);
Another workaround would be to switch the partition to a staging table, moving that table to a new filegroup with
an online index rebuild and switching the table back as the partition to the original table. This method requires some
planning and additional code to make it transparent to the client applications.
Let's look more closely at this approach. One of the key elements here is the view that works as another layer of
abstraction for the client code, hiding the staging table during the data movement process.
Let's create a table that stores data for the year 2014, partitioned on a monthly basis. The table stores the data up
to April in the FG1 filegroup using FG2 afterwards. You can see the code for doing this in Listing 15-19.
Listing 15-19. Using a temporary table to move partition data: Table and view creation
create partition function pfOrders(datetime2(0))
as range right for values
('2014-02-01','2014-03-01','2014-04-01'
,'2014-05-01','2014-06-01','2014-07-01');
create partition scheme psOrders
as partition pfOrders
to (FG1,FG1,FG1,FG1,FG2,FG2,FG2);
create table dbo.tblOrders
(
OrderId int not null,
OrderDate datetime2(0) not null,
OrderNum varchar(32) not null,
OrderTotal money not null,
CustomerId int not null,
/* Other Columns */
);
create unique clustered index IDX_tblOrders_OrderDate_OrderId
on dbo.tblOrders(OrderDate, OrderId)
on psOrders(OrderDate);