Partition Functions, Partition Scheme and Partitioned Table

Introduction

We have seen the advantages of Partitions which improves the performance of the query against large volume tables. We discussed in detail about the Range in the Partition. How we do achieve this. It’s time to get our hands dirty digging into the implementation of Partition Functions and Partition schemes.

Partition Functions

Partition function defines the number of partitions on the table. This is the first step in the implementation of a Partition on a database object and a single partition function can be used for multiple objects. The type of partition is also specified in the partition function, which currently can only be 'RANGE'.

Based on the fact about boundary values for partitions that which partition they should belong to, we can divide partition function into two types:

1. Left: The first value is the maximum value of the first partition.
2. Right: The first value is the minimum value of the second partition.
   

Syntax to create a partition function

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

input_parameter_type supported is generally numeric. The list of partition functions in the database can be queried using the sys.partition_functions catalogue view.

The first thing that you would want to do is to test whether your partition function is implemented as per your desire or not. Specially, we can check if it is working on boundary values. You can check it with the special function provided: $Partition. We test the partition function MyPartitionFunc2 created by us earlier. In this SQL, we are verifying to which partition, (Partition Key = 100), would belong to.

Select $PARTITION.MYPARTITIONFUNC2 100 [PARTITION NUMBER]

Partition Scheme

This is the physical storage scheme that will be followed by the partition. To define scheme, different file groups are specified, which would be occupied by each partition. It must be remembered that all partitions may also be defined with only one file group.

After the definition of a partition function, a partition scheme is defined. The partition scheme just like specifying an alignment for data i.e. it specifies the specific file groups used during partitioning an object. Though it is possible to create all partitions on PRIMARY but it would be best if these different partitions are stored in a separate file groups. This gives some performance improvement even in the case of single core computers. It would be best if these file groups are on different discs on a multi core processing machine.

The syntax for creating partition schema is as follows:

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

Partitioned Table

After creation of a partition scheme, a table may be defined to follow that scheme. These index and view may be based on different partition strategy (partition function and partition scheme).Now you might be wondering whether your existing tables could be partitioned or not. For partitioning your existing table just drop the clustered index on your table and recreate it on the required partition scheme.

Syntax to create a table on the Partition scheme.

CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition> }
[ <table_constraint> ] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ] [ ; ]

To check if the table is partitioned or not ->  Select the table and Right click ->  Properties -> Storage -> check the bit column for the “Table is Partitioned”

We can discuss about Partitioned Indexes in detail in my next  post.

Happy Learning!!!