Data Table partitioning in SQL Server 2005 1

Data partitioning, a new feature added to SQL Server 2005, provides a way to divide large tables and indexes into smaller parts. By doing so, it makes the life of a database administrator easier when doing backups, loading data, recovery and query processing.

Data partitioning improves the performance, reduces contention and increases availability of data.

This series of articles is going to illustrate table partitioning on different file groups, backups, recovery of portion of tables, etc.

A Table can be partitioned based on any column in the table. Microsoft defines that column as the partition key.

Step 1

Let us assume that we have a database, "Data Partition DB," with three different file groups, as shown below.

USE [master]
GO
/****** Object:  Database [Data Partition DB]    Script Date: 10/08/2006 23:09:53 ******/
  IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Data Partition DB')
DROP DATABASE [Data Partition DB]
GO
 CREATE DATABASE [Data Partition DB]
      ON PRIMARY
       (NAME='Data Partition DB Primary FG',
          FILENAME=
          'C:\Data\Primary\Data Partition DB Primary FG.mdf',
        SIZE=5,
        MAXSIZE=500,
        FILEGROWTH=1 ),
      FILEGROUP [Data Partition DB FG1]
       (NAME = 'Data Partition DB FG1',
          FILENAME =
         'C:\Data\FG1\Data Partition DB FG1.ndf',
        SIZE = 5MB,
        MAXSIZE=500,
        FILEGROWTH=1 ),
      FILEGROUP [Data Partition DB FG2]
       (NAME = 'Data Partition DB FG2',
          FILENAME =
         'C:\Data\FG2\Data Partition DB FG2.ndf',
        SIZE = 5MB,
        MAXSIZE=500,
        FILEGROWTH=1 ),
      FILEGROUP [Data Partition DB FG3]
       (NAME = 'Data Partition DB FG3',
          FILENAME =
         'C:\Data\FG3\Data Partition DB FG3.ndf',
        SIZE = 5MB,
        MAXSIZE=500,
        FILEGROWTH=1 )

When you check the property of the database, you see the FileGroup and Path as shown in Fig 1.0.

Fig 1.0

Note: In reality, the path will not be on C:\Data. Usually it will be on three different drives that help in boosting the performance.

Step 2

Partitioning of the tables depends on the partition range defined by Partition Function. Let us assume that we are going to partition the table into two halves, onto two different file groups. Let us also assume that the first half is going to have 100,000 rows and the remainder is going to be the second half. The partition function for this requirement can be created as follows:

use [Data Partition DB]
GO
CREATE PARTITION FUNCTION [Data Partition Range](int)
          AS RANGE LEFT FOR VALUES (100000)

Step 3

Partition Function is not useful unless it is associated with the proper file groups that we have created. Let us assume that we are going to use file group [Data Partition DB FG1] and [Data Partition DB FG2] for the partition table that we are going to create. This can be created as shown below.

USE [Data Partition DB]
go
CREATE PARTITION SCHEME [Data Partition Scheme]
          AS PARTITION [Data Partition Range]
        TO ([Data Partition DB FG1], [Data Partition DB FG2]);

Step 4

Now let us create the actual table that we want to partition using the created partition scheme. The CREATE TABLE statement should contain the partition key and the partition scheme to be used. This can be created as shown below.

USE [Data Partition DB]
go
CREATE TABLE MyTable
       (ID INT NOT NULL,
          Date DATETIME,
        Cost money)
      ON [Data Partition Scheme] (ID);

Step 5

Now let us create an index on the partitioned table. An index on a table improves performance. When both the indices and the table use the same partitioning function and the same partitioning columns, the table and index are said to be aligned. This can be created as shown below.

USE [Data Partition DB]
  go
CREATE UNIQUE CLUSTERED INDEX MyTable_IXC
       ON MyTable(ID)
       ON [Data Partition Scheme]  (ID)

Step 6

Now let us insert some data on to the table MyTable using the following T-SQL statements.

USE [Data Partition DB]
go
declare @count int
set @count =1
while @count <=100
  begin
insert into MyTable select @count,getdate(),100.00
set @count=@count+1
end
set @count =100002
while @count <=100202
begin
insert into MyTable select @count,getdate(),200.00
set @count=@count+1
  end

The above TSQL statement inserted 100 rows in Data Partition DB FG1 and 100 rows in Data Partition DB FG2.

Let us query the table using the T-SQL statement as shown below

select * from MyTable

You will get the following results, shown below.

.
  .
.
95 2006-10-09 00:38:26.500 100.00
96 2006-10-09 00:38:26.500 100.00
97 2006-10-09 00:38:26.500 100.00
98 2006-10-09 00:38:26.500 100.00
99 2006-10-09 00:38:26.500 100.00
100 2006-10-09 00:38:26.500 100.00
  100002 2006-10-09 00:38:26.500 200.00
.
.
.

Conclusion:

This article illustrated Data partitioning, the new feature introduced in SQL Server 2005 by Microsoft. The subsequent article in this series will illustrate how to modify the partition function and partition schemes and how to handle file groups, etc.