Creating Partitioned Tables

The following are the steps to create a partitioned table.

1.) Create a partition function

2.) Create a partition scheme

3.) Create a table using the partition scheme.

Creating a Partition Function

Partition function is used to figure out how to map a data type to a particular partition using boundary points to establish the domain of the partition. In simple it can be said as below

Maps ranges of a data type to integer values

Defined by specifying boundary points

N boundary points define N+1 partitions

Syntax:
<

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [ boundary_value [ ,…n ] ] )

Eg.

CREATE PARTITION FUNCTION month_range (tinyint)
as RANGE RIGHT
for values
( -- Partition 1 -- > 6th month and earlier
'07', -- Partition 2 -- > 7th month
'08', -- Partition 3 -- > 8th month
'09' -- Partition 4 -- > 9th month and later
)

Range right means if you have a value = to boundary point, it goes into that partition on the right. Range right is the most natural partitioning scheme because every boundary is the starting point for that partition. Range left means if you have a value = to boundary point, it goes into the partition to the left.

Creating a Partition Scheme

Partition scheme is used to map the partition defined by the partition function to an underlying storage location. It can be mapped to same or different storage locations, this will be helpful in manageability and filegroup based backup. Categories of storage(i.e Raid 0, Raid 1 etc) can be used. For high performance, spread partition filegroup across as many drives as possible. A partition scheme can use only one partition function. However, a partition function can participate in more than one partition scheme.

Syntax:

CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )

Eg.

CREATE PARTITION SCHEME month_scheme
as PARTITION month_range to
(
month_earlier, -- filegroup for data's before 7th month
month_7, -- filegroup for 7th month
month_8, -- filegroup for 8th month
month_later -- filegroup for 9th month and later
)

You cant create partition scheme until you create the respective filegroups as mentioned above(month_earlier, month_7 etc) in the particular db, else you will receive a error as show below.

To create the filegroup use the below query.

USE master
GO
ALTER DATABASE ADD FILEGROUP Filegroupname;
GO

After creating the filegroup I got it created.

This illustrates both partition function shown at the top, establishing range boundaries (N) that are used to identify the partition # (N+1). Each partition is mapped to N+1 physical storage locations or file groups using the partition scheme shown at the bottom.