Carrying Out the Partitioning

Partition Function

The partition function calculates the logical partition number for any specific record based on the record’s field value. We only need to consider the value of the 'queue' field since we want to partition data according to queues.

The following is an example of the partition function:

CREATE PARTITION FUNCTION [QNamePFN](varchar(64)) AS
RANGE RIGHT FOR VALUES (N'Archive', N'Distribution', N'Inbound')
GO

Note that an SQL server partition function is always a range function. The values for the range function must be sorted in ascending order so that you can clearly see which range any particular value falls into.

In the example above, all data that comes earlier in the alphabet than Archive is placed in the first partition. All data whose alphabetical order is the same or later than Archive but earlier than Distribution is placed in the second partition. All data whose alphabetical order is the same or later than Distribution but earlier than Inbound is placed in the third partition. All other data is placed in the forth partition.

Note that it is the partition scheme that assigns a specific partition to the range; you can actually assign different ranges to the same partition.

Also, this partition function applies to all queues in the Business Process. For example, if there is a queue Begin it falls into the second range and will be assigned to the same partition as the Archive queue. But if Begin is not a Type 1 queue this result may be less than ideal. One way to ensure that every queue is assigned to its intended partition is to list all the queues in the Business Process in alphabetical order in the partitioning function, and then specify the appropriate partition for each queue. If a new queue is added to the Business Process, you can alter the partition function and partition scheme to take account of this new queue.

Partitioning Scheme

The partitioning scheme uses the partitioning function to define which records (with a particular value of the partitioning function) go to which partition.

CREATE PARTITION SCHEME [QNamePScheme]
AS PARTITION [QNamePFN] TO ([PRIMARY], [P1], [P2], [P3])
GO

Since our partitioning function is based solely on the value of the 'queue' field, our partitioning scheme tells the database which queue goes to which partition.

Partitioning the Table

To partition the table, simply specify the partitioning scheme for the table:

create table interactions
(
id varchar(16) not null,
...
) on QNamePScheme(queue)
Go

Note that we explicitly specify that the queue field value should be given to the partitioning scheme and subsequently to the partitioning function to decide which partition the record should go to.