Here is a script that shows how to setup a partitioned database. This can be useful to increase the performance of database by spreading the data across various drives for parallel i/o. In my case it was interesting to manage the life cycle of data by using a set of files corresponding to times in the year. The idea is to setup partitions for an arbitrarily long period. Then as data is no longer needed the corresponding filegroups can be dropped and the corresponding file archived away. There is a way to do this with rolling filegroups, the problem is that the file name no longer indicate to what period of time it belongs.
-- Usefull links:
-- http://sqlblog.com/blogs/sarah_henwood/archive/2007/07/23/validating-what-is-stored-in-a-partition-filegroup.aspx
-- http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx
USE [master]
GO
CREATE DATABASE [PartinionedDB] ON PRIMARY
( NAME = N'PrimaryFileName', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartinionedDB.mdf' , SIZE = 6336KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'PrimaryLogFileName', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartinionedDB_log.ldf' , SIZE = 10176KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
-- Add filegroups that will contains partitioned values
ALTER DATABASE PartinionedDB ADD FILEGROUP fgBefore2008;
ALTER DATABASE PartinionedDB ADD FILEGROUP fg2008Quarter1;
ALTER DATABASE PartinionedDB ADD FILEGROUP fg2008Quarter2;
ALTER DATABASE PartinionedDB ADD FILEGROUP fg2008Quarter3;
ALTER DATABASE PartinionedDB ADD FILEGROUP fg2008Quarter4;
ALTER DATABASE PartinionedDB ADD FILEGROUP fg2009Quarter1;
ALTER DATABASE PartinionedDB ADD FILEGROUP fg2009Quarter2;
ALTER DATABASE PartinionedDB ADD FILEGROUP fg2009Quarter3;
ALTER DATABASE PartinionedDB ADD FILEGROUP fg2009Quarter4;
ALTER DATABASE PartinionedDB ADD FILEGROUP fgAfter2009;
-- Add files to filegroups
ALTER DATABASE PartinionedDB ADD FILE (NAME = 'fF08Q1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fF08Q1.ndf', SIZE = 2 MB ,FILEGROWTH = 2 MB ) TO FILEGROUP fg2008Quarter1;
ALTER DATABASE PartinionedDB ADD FILE (NAME = 'fF08Q2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fF08Q2.ndf', SIZE = 2 MB ,FILEGROWTH = 2 MB ) TO FILEGROUP fg2008Quarter2;
ALTER DATABASE PartinionedDB ADD FILE (NAME = 'fF08Q3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fF08Q3.ndf', SIZE = 2 MB ,FILEGROWTH = 2 MB ) TO FILEGROUP fg2008Quarter3;
ALTER DATABASE PartinionedDB ADD FILE (NAME = 'fF08Q4', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fF08Q4.ndf', SIZE = 2 MB ,FILEGROWTH = 2 MB ) TO FILEGROUP fg2008Quarter4;
ALTER DATABASE PartinionedDB ADD FILE (NAME = 'fF09Q1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fF09Q1.ndf', SIZE = 2 MB ,FILEGROWTH = 2 MB ) TO FILEGROUP fg2009Quarter1;
ALTER DATABASE PartinionedDB ADD FILE (NAME = 'fF09Q2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fF09Q2.ndf', SIZE = 2 MB ,FILEGROWTH = 2 MB ) TO FILEGROUP fg2009Quarter2;
ALTER DATABASE PartinionedDB ADD FILE (NAME = 'fF09Q3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fF09Q3.ndf', SIZE = 2 MB ,FILEGROWTH = 2 MB ) TO FILEGROUP fg2009Quarter3;
ALTER DATABASE PartinionedDB ADD FILE (NAME = 'fF09Q4', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fF09Q4.ndf', SIZE = 2 MB ,FILEGROWTH = 2 MB ) TO FILEGROUP fg2009Quarter4;
ALTER DATABASE PartinionedDB ADD FILE (NAME = 'fBefore08', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fBefore08.ndf', SIZE = 2 MB ,FILEGROWTH = 2 MB ) TO FILEGROUP fgBefore2008;
ALTER DATABASE PartinionedDB ADD FILE (NAME = 'fAfter09', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fAfter09.ndf', SIZE = 2 MB ,FILEGROWTH = 2 MB ) TO FILEGROUP fgAfter2009;
USE [PartinionedDB]
GO
-- Create Partition Function and Scheme
CREATE PARTITION FUNCTION pf_MyFyQuarters (DATETIME) AS
RANGE RIGHT FOR VALUES
(
'2008-01-01 00:00:00','2008-04-01 00:00:00','2008-07-01 00:00:00','2008-10-01 00:00:00', -- 2008
'2009-01-01 00:00:00','2009-04-01 00:00:00','2009-07-01 00:00:00','2009-10-01 00:00:00'); -- 2009
GO
CREATE PARTITION SCHEME ps_MyFyQuarters
AS PARTITION pf_MyFyQuarters
TO
(fgBefore2008, -- partition 1
fg2008Quarter1,fg2008Quarter2,fg2008Quarter3,fg2008Quarter4, -- partition 2, 3, 4, 5
fg2009Quarter1,fg2009Quarter2,fg2009Quarter3,fg2009Quarter4, -- 6, 7, 8, 9
fgAfter2009); -- partition 10
GO
-- Create a table that uses the partition function
CREATE TABLE PartitionTable
(
Id UNIQUEIDENTIFIER,
DateColumn DATETIME NOT NULL,
ValueA INTEGER,
ValueB VARCHAR(50)
)
ON ps_MyFyQuarters (DateColumn)
GO
-- Insert Data and check where it is stored
INSERT INTO PartitionTable(Id, DateColumn, ValueA, ValueB) VALUES('5D72B397-A066-43A4-B409-117DED69C41E', '09/30/2004', 10, 'AAA');
INSERT INTO PartitionTable(Id, DateColumn, ValueA, ValueB) VALUES('BA9403BE-A7B2-4209-807E-2F90F04A1B72', '09/30/2009', 20, 'BBB');
INSERT INTO PartitionTable(Id, DateColumn, ValueA, ValueB) VALUES('4A6283CB-4C63-4F6B-8D16-AFB4D36E4036', '09/30/2008', 30, 'CCC');
INSERT INTO PartitionTable(Id, DateColumn, ValueA, ValueB) VALUES('F268C4DA-7A88-49D2-B423-E15622D9BC00', '04/30/2009', 40, 'DDD');
SELECT * FROM PartitionTable
WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 1
SELECT * FROM PartitionTable
WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 5
SELECT * FROM PartitionTable
WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 6
SELECT * FROM PartitionTable
WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 7
SELECT * FROM PartitionTable
WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 8
