Creation of Table Partitions in SQL Server 2005

In the database world, partitioning is the horizontal splitting of rows in a table or index defined on a table in separate, smaller logical units. In fact, every table in SQL Server 2005 has at least one partition. You are essentially breaking one table or index up into smaller tables or indexes that act as one table or index. This can greatly increase performance because you are querying several smaller tables rather than one much larger one. The advantages increase when the server has multiple processors, which allow the partitions to be queried in parallel in the same query, and when the partitions are spread across multiple file groups.

Basically partitions help in improving the performance, scalability and managing of the large tables. As the table grows larger and larger the performance in accessing the data is affected, scalability and managing issues arises. With the help of partitioning a table we can achieve a great level of performance and managing of tables.

Let us see in how we can create partitioned tables in detail. The procedure for creating a partitioned table is as follows.

Beginning with this we have to have various filegroups for the database if we need to place the partitioned tables on different filegroups. We already discussed how to filegroups created in the last post.

1. Creation of Partition Function.

When a table is partitioned, it is broken horizontally into smaller table partitions that are used together to assimilate the whole table. To do this, SQL Server has to know how to break the table into smaller parts. This is where a Partition Function comes into play. A Partition Function is the logic that maps the rows of a table or index into predefined partitions based on the values in a field of data.

The partition function is created for setting the range partitions. The ranges can be set for a lower or upper threshold. The syntax is as follows

CREATE PARTITION FUNCTION ()

AS

RANGE LEFT/RIGHT FOR VALUES (,,…)

RANGE LEFT: Specifies that partition values will be less than or equal to the values defined in the Partition Function.

RANGE RIGHT: Specifies that partition values will be less than the values defined in the Partition Function.

For Example,

CREATE PARTITION FUNCTION [testPartition](int) AS RANGE LEFT
FOR VALUES (1000, 2000, 3000)

This creates a partitions function with ranges for an integer column having thresholds 1000, 2000, 3000. So this indicates there would be three partitions where first partition would be less than equal to 1000, the second between 1001 and less than equal to 2000, the third one between 2001 and less than equal to 3000.

2. Creation of Partition Scheme

You tell SQL Server how to horizontally split a table with a Partition Function. You also need to indicate how the partitions will be stored in the database. In SQL Server 2005, you map a table partition to a filegroup, which is basically a logic grouping that the database uses to store its data files and transaction log files. Every database has at least one filegroup named the Primary filegroup and additional filegroups for administrative and performance purposes. In a Partition Scheme, you can define that all partitions are mapped to the same filegroup, or you can use the scheme to split up the partitions across filegroups. The advantage is that, when the filegroups are split across individual disks, SQL Server will be better equipped to use resources when retrieving data. This advantage becomes even greater when you are running queries against the partitioned tables on a server with multiple processors.

The syntax for creating Partition scheme,

CREATE PARTITION SCHEME

AS

PARTITION

TO (,,….)

For Example,

CREATE PARTITION SCHEME [testPartitionScheme] AS PARTITION [testPartition]
ALL TO ([PRIMARY])

3. Creation of Partition Table

The next step is to create the partitioned table which would be associated with the defined partition scheme

Example partition table for the above partition scheme as follows,

IF OBJECT_ID('customer')>0
DROP TABLE [customer];
GO
CREATE TABLE [dbo].[customer]
(
[Id] [int] IDENTITY(1,1),
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50),
[Company] [varchar](50) NULL,
[Email] [varchar](100),
)ON [testPartitionScheme](Id)
GO
DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=10000)
BEGIN
INSERT INTO customer(FirstName, LastName, Company,Email)
VALUES('Vinoth', 'kumar', 'myComp', 'myId@my.com' )
INSERT INTO customer(FirstName, LastName, Company,Email)
VALUES('Krishna','kumar', 'myComp', 'myId@my.com' )
INSERT INTO customer(FirstName, LastName, Company,Email)
VALUES('Mohan','kumar', 'myComp', 'myId@my.com' )
SET @i = @i + 1
END

Hence using the above 3 steps, we can create the partitioned table.

I’m going to make sure the partitions are working correctly by running some queries on the data.

The following querry will return all partitions that are mapped to the customer table ,

SELECT * From sys.partitions
WHERE OBJECT_NAME(object_id) = 'customer'

The following query returns all rows from the customer table,

SELECT $partition.[testPartition](Id), *
FROM customer

Query for the data from a particular partition,

SELECT * FROM customer WHERE $PARTITION.[testPartition](Id)=2

Query for Knowing the Partition Number,

SELECT $PARTITION.[testPartition](1654)

Query for find the count of records in each partition,

SELECT $PARTITION.[testPartition](Id) AS Partition, COUNT(*) AS [COUNT] FROM customer GROUP BY $PARTITION.[testPartition](Id)ORDER BY Partition ;

The partitions can be split by splitting the partition ranges. The splitting is done by using the alter partition command. We have to note that before we split the partition there should be a additional filegroup already associated in the partition scheme. If there is no unused filegroup available then we cannot split. So before splitting we have to ensure that a filegroup is added to the partition scheme. This is as shown below

Query for splitting partition,

ALTER PARTITION FUNCTION testPartition() SPLIT RANGE(75)

This would create a new partition range between >75 and less than equal to 1000.

Merging of Partition,

The partitions can be merged by merging the partition ranges. The partition range value mentioned will merge that to the next greater partition range value into a singe partition. This is as shown below.

Query for merging partition,

ALTER PARTITION FUNCTION testPartition() MERGE RANGE(2000)

This returns a new merged partitions for 2000 and 75 . So now there would be only 75, 1000, 2001 as the new range values for partitions