Partitioning Data

Table Partitioning is a new features define in Sql Server 2005. When database size increases too more , then it is very difficult to handle the data for maintenance like update or retrieve the record from the table.Data partition increase performance , reduce contention and increase availability of the data.

Creating a partitioned table or index typically happens in four parts :

1. Create a filegroup or filegroups and corresponding files that will hold the partitions specified by the partition scheme.
2. Create a partition functions that maps the rows of a table or index into partitions based on the values of a specified column.
3. Create a partition scheme that maps the partitions of a partitioned table or index to the new filegroups.
4. Create or modify a table or index and specify the partition scheme as the storage locations.

Step 1 . Now first of all we will create file groups of the database.

Lets assume that we have 3 different file groups in the database DB_DATA, we will create 3 file groups as below.



Step 2
A partition function defines the boundary points that will used to split data across a partition scheme.

An example of partition function is

CREATE PARTITION FUNCTION
TablePartition(int)
AS RANGE LEFT
FOR VALUES (10,20,30,40,50,60)

Each partition function requires a name and a data type. The data type for a partition function can be any native SQL Server Data type , except text , ntext , image , varbinary(max) , timestamp , xml and varchar(max).

A partition function always maps  the entire range of data ;therefore ,no gaps are present.You cannot specify duplicate boundary points. This ensures that any value stored in a column always evaluates to a single partition. Null values are always stored in the leftmost partition until you explicitly specify null as a boundary and use the RANGE RIGHT syntax, in which case nulls are stored in the rightmost partition.

You can have a maximum of 1000 partitions for an object ; therefore you are allowed to specify a maximum of 999 boundary points.

Step 3 : (Creating a partition schema)

A partition schema defined the storage structures and collection of file groups that you want to use with a given partition function.

You can define partition schema as below

CREATE PARTITION SCHEME tablePartScheme AS PARTITION tablePartFunction TO (Filegroup 1,Filegroup2,Filegroup3, Filegroup4,Filegroup5)

or

CREATE PARTITION SCHEME tablePartSchem AS PARTITION tablePartFunction TO (Filegroup1, Filegroup1,Filegroup2,Filegroup2,Filegroup3)

or

CREATE PARTITION SCHEME tablePartScheme AS PARTITION tablePartFunction ALL TO (Filegroup 1)

Here ALL keyword which allows you to create all partition defined by the partition function within the