Monday, September 10, 2012

SQL Server 2012-Database Partitioning...

One of the issues that we frequently face in databases is how to query large amounts of data in a table. One of the suggested methods is to use partitioning of tables and indexes. In case of data partitioning the data in the table is partitioned horizontally and all the partitions of the tables need to be reside in the same database.
In SQL Server 2012 the number of partitions supported has increased to 15,000, earlier it was limited to 1000.  One of the benefits of using partitioning is transfer or access parts of data efficiently. One of the scenarios were this would help is when loading OLAP systems from OLTP databases. There are a some concepts which are central to table partitioning. They are:
Partition Function: A database object that defines how rows of data are partitoned based on a partitioning column.
Partition Scheme: This is helps map the Partition of a partition function to a set of filegroups. This helps perfomance of backup operations of filegroups.
Partitioning column: The column or index of a table that is used to partition a table or an index.
One of the common scenarios where partitioning is implemented is the sliding window scenario. Please take a look at the following link (though it is for SQL Server 2005) it gives us a good idea of how to implement the partition in SQL Server:

Let us say if we wanted to create a partition on Sale Date and have data stored for every month, one of the ways to do it is:
(Please note: Partition Function can only be created in Enterprise Edition of SQL Server, only Enterprise Edition Supports Partitoning).

create partition function pfMonthly (datetime)
as RANGE RIGHT for values('2012-01-01','2012-02-01','2012-03-01','2012-04-01')

CREATE PARTITION SCHEME pfMonthly as partition pfMonthly all
to ([primary])

One can use the following objects to check the partition and the range of values for partition:

select * from sys.partition_range_values
where function_id in (select function_id
from sys.partition_functions
where name in ('pfMonthly'))

No comments:

Post a Comment