Data Partitioning - Pro SQL Server Internals - page 322

Listing 15-21. Using a temporary table to move partition data: Altering the view
alter view dbo.Orders(OrderId, OrderDate, OrderNum
,OrderTotal, CustomerId /*Other Columns*/)
with schemabinding
as
select OrderId, OrderDate, OrderNum
,OrderTotal, CustomerId /*Other Columns*/
from dbo.tblOrders
union all
select OrderId, OrderDate, OrderNum
,OrderTotal, CustomerId /*Other Columns*/
from dbo.tblOrdersStage
go
create trigger dbo.trgOrdersView_Ins
on dbo.Orders
instead of insert
as
if @@rowcount = 0 return
set nocount on
if not exists(select * from inserted)
return
insert into dbo.tblOrders(OrderId, OrderDate
,OrderNum, OrderTotal, CustomerId)
select OrderId, OrderDate, OrderNum
,OrderTotal, CustomerId
from inserted
where
OrderDate < '2014-05-01' or
OrderDate >= '2014-06-01'
insert into dbo.tblOrdersStage(OrderId, OrderDate
,OrderNum, OrderTotal, CustomerId)
select OrderId, OrderDate, OrderNum
,OrderTotal, CustomerId
from inserted
where
OrderDate >= '2014-05-01' and
OrderDate < '2014-06-01'
Now you can move the staging table to the FG1 filegroup by performing an index rebuild, as shown in
Listing 15-22. It is worth repeating that if the table has LOB columns, it cannot work as an online operation in SQL
Server 2005-2008R2. Moreover, you will need to use a workaround and rebuild the indexes in the new partition
scheme to move the LOB_DATA allocation units, as was shown earlier in Listing 15-14.
Listing 15-22. Using a temporary table to move partition data: Moving the staging table
create unique clustered index IDX_tblOrdersStage_OrderDate_OrderId
on dbo.tblOrdersStage(OrderDate, OrderId)
with (drop_existing=on, online=on)
on [FG1];