Database Reference
In-Depth Information
You can create new (split) or drop existing (merge) partitions by altering the partition scheme and functions. The
code in Listing 15-2 merges the two leftmost and split rightmost partitions in the
OrdersPT
table. After the split, the
two rightmost partitions of the table will store data with an
OrderDate
for July 2014, equal to or greater than 2014-08-01,
respectively.
Listing 15-2.
Splitting and merging partitions
/* Merging two left-most partitions */
alter partition function pfOrders() merge range('2012-02-01')
go
/* Splitting right-most partition */
-- Step 1: Altering partition scheme - specifying FileGroup
-- where new partition needs to be stored
alter partition scheme psOrders next used [FASTSTORAGE];
-- Step 2: Splitting partition function
alter partition function pfOrders() split range('2014-08-01');
One of the most powerful features of table partitioning is the ability to switch partitions between tables. That
dramatically simplifies the implementation of some operations, such as purging old data or importing data into
the table.
■
Note
We will discuss implementing data purge and sliding window patterns later in the chapter.
Listing 15-3 shows you how to import new data into the table
MainData
by switching another staging table,
StagingData
, as the new partition. This approach is very useful when you need to import data from external sources
into the table. Even though you can insert data directly into the table, a partition switch is a metadata operation,
which allows you to minimize locking during the import process.
Listing 15-3.
Switching a staging table as the new partition
create partition function pfMainData(datetime)
as range right for values
('2014-02-01', '2014-03-01','2014-04-01','2014-05-01','2014-06-01'
,'2014-07-01','2014-08-01','2014-09-01','2014-10-01','2014-11-01'
,'2014-12-01');
create partition scheme psMainData
as partition pfMainData
all to (FG2014);
/* Even though we have 12 partitions - one per month, let's assume
that only January-April data is populated. E.g. we are in the middle
of the year */
create table dbo.MainData
(
ADate datetime not null,
ID bigint not null,
CustomerId int not null,