Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 17, 2006

Data partitioning in SQL Server 2005 - Part I

By Muthusamy Anantha Kumar aka The MAK

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]
/****** 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]
 CREATE DATABASE [Data Partition DB]
       (NAME='Data Partition DB Primary FG',
          'C:\Data\Primary\Data Partition DB Primary FG.mdf',
        FILEGROWTH=1 ),
      FILEGROUP [Data Partition DB FG1]
       (NAME = 'Data Partition DB FG1',
        FILENAME =
         'C:\Data\FG1\Data Partition DB FG1.ndf',
        SIZE = 5MB,
        FILEGROWTH=1 ),
      FILEGROUP [Data Partition DB FG2]
       (NAME = 'Data Partition DB FG2',
        FILENAME =
         'C:\Data\FG2\Data Partition DB FG2.ndf',
        SIZE = 5MB,
        FILEGROWTH=1 ),
      FILEGROUP [Data Partition DB FG3]
       (NAME = 'Data Partition DB FG3',
        FILENAME =
         'C:\Data\FG3\Data Partition DB FG3.ndf',
        SIZE = 5MB,
        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]
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]
        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]
       (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]
       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]
declare @count int
set @count =1
while @count <=100
insert into MyTable select @count,getdate(),100.00
set @count=@count+1
set @count =100002
while @count <=100202
insert into MyTable select @count,getdate(),200.00
set @count=@count+1

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


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.

» See All Articles by Columnist MAK

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM