What is Table Partitioning?
Table partitioning is built-in method of horizontally partitioning data within a table and/or index still maintaining the table as a single logical object. Partitioning keeps the same no of columns in each partition, but reducing the number of rows.
Why do we need Table Partitioning?
- Easy management of large tables and indexes.
- Decrease load time.
- Improve query performance.
- Provide small window for maintenance, means easy to maintain chunks of data instead of whole.
How Partitioning is done?
- Create file -groups to hold the partitions.
- Add files to each file -group in the partitioning.
- Create Partition Function.
- Create Partition Scheme.
- Create table , binding a specific column to a Partition Scheme.
Below is an example of partitioning a table:
Create a sample database to be used for partitioning. I have added two file groups other than the primary file group to it.
CREATE DATABASE [PartitionDB] ON
PRIMARY
( NAME = N'PartitionDB',
FILENAME = N'C:\PartitionDB\PartitionDB.mdf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB
),
FILEGROUP [FG2]
( NAME = N'PartitionDB_2',
FILENAME = N'C:\PartitionDB\PartitionDB_2.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB
)
FILEGROUP [FG3]
( NAME = N'PartitionDB_3',
FILENAME = N'C:\PartitionDB\PartitionDB_3.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB
)
LOG ON
( NAME = N'PartitionDB_log',
FILENAME = N'C:\PartitionDB\PartitionDB_log.ldf' ,
SIZE = 9216KB , FILEGROWTH = 10%
)
Now we create a Partition Function. Partition Function can be used for any Partition Scheme. Please read the below link for specific details about the partition function:
CREATE PARTITION FUNCTION EqualFunc (INT)
AS RANGE LEFT
FOR VALUES (0,1)
Next step is to create a Partition Scheme. Here is the link for Partition Scheme:
CREATE PARTITION SCHEME EqualScheme
AS PARTITION EqualFunc
TO (FG2, FG3, [PRIMARY])
Finally we create a table using the Partition Scheme that we created. Based on the value of ID column PartitionElement will be computed and that will be used in deciding on which file group the row will be stored.
CREATE TABLE Customer
(
ID INT,
Name VARCHAR(20),
PartitionElement AS ID % 3 PERSISTED
)
ON EqualScheme (PartitionElement)
INSERT INTO Customer(ID,Name) VALUES(3,'abc3')
INSERT INTO Customer(ID,Name) VALUES(1,'abc1')
INSERT INTO Customer(ID,Name) VALUES(2,'abc2')
Below query will determine the rows in each partition that is created for the table.
SELECT partition_id, object_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('Customer')
If we want to get the resultset for the particular partition here is the query
SELECT *
FROM Customer
WHERE $PARTITION.EqualFunc(PartitionElement) = 2
It is advisable to keep the filegroups on different disk drives for parallelism. Partitioning is a great way to improve performance for the tables with large amount of data.
