Data Partitioning - Pro SQL Server Internals - page 321

create nonclustered index IDX_tblOrders_CustomerId
on dbo.tblOrders(CustomerId)
on psOrders(OrderDate);
go
create view dbo.Orders(OrderId, OrderDate, OrderNum
,OrderTotal, CustomerId /*Other Columns*/)
with schemabinding
as
select OrderId, OrderDate, OrderNum
,OrderTotal, CustomerId /*Other Columns*/
from dbo.tblOrders;
As you can see, the script creates an updateable Orders view in addition to the table. All access to the data should
be done through that view.
Let's assume that you want to move May 2014 data to the FG1 filegroup. As a first step, you need to create a staging
table and switch May's partition to there. The table must reside in the FG2 filegroup and have a CHECK constraint
defined. The code for accomplishing this is shown in Listing 15-20.
Listing 15-20. Using a temporary table to move partition data: Switching the partition to the staging table
create table dbo.tblOrdersStage
(
OrderId int not null,
OrderDate datetime2(0) not null,
OrderNum varchar(32) not null,
OrderTotal money not null,
CustomerId int not null,
/* Other Columns */
constraint CHK_tblOrdersStage
check(OrderDate >= '2014-05-01' and OrderDate < '2014-06-01')
);
create unique clustered index IDX_tblOrdersStage_OrderDate_OrderId
on dbo.tblOrdersStage(OrderDate, OrderId)
on [FG2];
create nonclustered index IDX_tblOrdersStage_CustomerId
on dbo.tblOrdersStage(CustomerId)
on [FG2];
alter table dbo.tblOrders
switch partition 5 to dbo.tblOrdersStage;
Now you have data in two different tables, and you need to alter the view, making it partitioned. That change
allows the client applications to read the data transparently from both tables. However, it would prevent the view from
being updateable. The simplest way to address this is to create INSTEAD OF triggers on the view.
You can see the code for doing this in Listing 15-21. It shows only one INSTEAD OF INSERT trigger statement in
order to save space in this topic.