By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP

October 13, 2007

Horizontal partitioning is one of the most effective ways to increase database systems performance. Prior to SQL Server 2005 you had to create your own partitioning scheme and maintain it. You may have partitioned a table based on the age of the information in a row. For example, first partition, names TableName, rows added in the last 30 days, second partition, named TableNameHistory, rows added more than 30 days ago. You had a night job setup which moved the 31st day data over to the second partition (manual sliding window). Applications had to know where to look for a particular row based on the 30 day split.

Aged data is accessed less frequently than new data. Latest federal laws and regulations require some businesses like banks to keep aged data online for immediate access as long as up to 7 to 10 years. Hence, it is critical that an enterprise manages aged data effectively while maintaining high availability for existing data and facilitating the faster access of new data. Data aging can be handled effectively by the sliding window methodology. If the data is partitioned by age, a sliding window implementation is possible. If data is partitioned horizontally, piecemeal backup in SQL Server 2005 can be implemented, more frequent for recent data, less frequent for old data. Piecemeal (database file or filegroup) backup and restore operations in SQL Server 2005 offer more flexibility for managing partitions.

SQL Server 2005 introduces automatic horizonal partitioning based on a column and specification of the ranges for the partition. In the example below we create a partition based on the StoreID. The ranges are Partition 1 (< 100), Partition 2 (100-299), Partition 3 (>= 300). The nice thing about the built-in horizontal partitioning in SQL Server 2005, that you don't have to worry about in which partition the data is located. You just operate on a partitioned table just like on a non-partitioned table. You will see significant performance improvement if partitioning is correctly implemented.

This is the code sample:

-- SQL Server table partitioning

-- SQL Server partitioning

USE Copy1OfAdventureWorks

GO

ALTER DATABASE Copy1OfAdventureWorks ADD FILEGROUP SecondaryAlpha

ALTER DATABASE Copy1OfAdventureWorks ADD FILEGROUP SecondaryBeta

ALTER DATABASE Copy1OfAdventureWorks ADD FILEGROUP SecondaryDelta

GO

ALTER DATABASE Copy1OfAdventureWorks

ADD FILE

( NAME = DataFile1,

FILENAME = 'f:\data\Copy1OfAdventureWorks\partition\datafile1.ndf',

SIZE = 10MB,

MAXSIZE = 1000MB,

FILEGROWTH = 10MB)

TO FILEGROUP SecondaryAlpha

ALTER DATABASE Copy1OfAdventureWorks

ADD FILE

( NAME = DataFile2,

FILENAME = 'f:\data\Copy1OfAdventureWorks\partition\datafile2.ndf',

SIZE = 10MB,

MAXSIZE = 1000MB,

FILEGROWTH = 10MB)

TO FILEGROUP SecondaryBeta

ALTER DATABASE Copy1OfAdventureWorks

ADD FILE

( NAME = DataFile3,

FILENAME = 'f:\data\Copy1OfAdventureWorks\partition\datafile3.ndf',

SIZE = 10MB,

MAXSIZE = 1000MB,

FILEGROWTH = 10MB)

TO FILEGROUP SecondaryDelta

GO

CREATE PARTITION FUNCTION fnStorePartition (int)

AS RANGE RIGHT FOR VALUES (100,300)

GO

CREATE PARTITION SCHEME schStorePartition AS

PARTITION fnStorePartition TO ( 'SecondaryAlpha','SecondaryBeta', 'SecondaryDelta')

GO

CREATE TABLE Sales.StorePartitioned (

  StoreID      INT    NOT NULL    PRIMARY KEY,

  [Name]       NVARCHAR(50),

  ModifiedDate DATETIME)

GO

INSERT INTO Sales.StorePartitioned

SELECT   CustomerID,

         [Name],

         ModifiedDate

FROM     Sales.Store

ORDER BY CustomerID

GO

SELECT StoreID,

       [Name],

       ModifiedDate,

       PARTITION =$PARTITION.fnStorePartition(StoreID)

FROM   Sales.StorePartitioned

GO

This is the partial result set: