Database Reference
In-Depth Information
OrderNum varchar(32) not null,
OrderTotal money not null,
CustomerId int not null,
/* Other Columns */
);
create unique clustered index IDX_OrderData_OrderDate_OrderId
on dbo.OrderData(OrderDate, OrderId)
on psOrderData(OrderDate);
create nonclustered index IDX_OrderData_CustomerId
on dbo.OrderData(CustomerId)
on psOrderData(OrderDate);
create table dbo.OrderDataTmp
(
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_OrderDataTmp_OrderDate_OrderId
on dbo.OrderDataTmp(OrderDate, OrderId)
on [FG1];
create nonclustered index IDX_OrderDataTmp_CustomerId
on dbo.OrderDataTmp(CustomerId)
on [FG1];
It is important to have both partitions predefined. The data will be inserted into the July 2014 partition as of
midnight of July 1st, before the purge process is running. The empty rightmost partition guarantees that the partition
split during the purge process will be done at the metadata level.
There is also an
OrderDataTmp
table created in the script, which we will use as the destination for partition switch
and purge. That table must reside in the same filegroup with the leftmost partition and have the same schema and
indexes defined.
The purge process is shown in Listing 15-28. It switches the leftmost partition to the temporary table and splits
the rightmost partition, creating a new empty partition for next month's run.
Listing 15-28.
Sliding Window scenario: Purge process
-- Purging old partition
alter table dbo.OrderData switch partition 1 to dbo.OrderDataTmp;
truncate table dbo.OrderDataTmp;
-- Creating new partition
alter partition scheme psOrderData next used [FG1];
alter partition function pfOrderData() split range('2014-09-01')
