SQL 2005 / 2008 : Table Partitioning - Run through

Example of table partitioning in SQL Server (versions 2005 & 2008)

1) Create a database! >

USE [master]
GO

CREATE DATABASE [Data_Source] ON  PRIMARY 
( NAME = N'Data_Source', FILENAME = N'D:\Data\Data_Source.mdf' , SIZE = 204800KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 

LOG ON 
( NAME = N'Data_Source_log', FILENAME = N'E:\logs\Data_Source_log.ldf' , SIZE = 1024000KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

2) Add Filegroups to the database >

USE [master]
GO
ALTER DATABASE [Data_Source] ADD FILEGROUP [2004]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2005]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2006]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2007]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2008]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2009]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2010]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2011]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2012]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2013]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2014]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2015]

3) Add data files into the filegroups >

ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2004Data', FILENAME = N'D:\Data\Data_Source_2004.mdf' , SIZE = 512KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2004]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2005Data', FILENAME = N'D:\Data\Data_Source_2005.mdf' , SIZE = 512KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2005]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2006Data', FILENAME = N'D:\Data\Data_Source_2006.mdf' , SIZE = 51200KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2006]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2007Data', FILENAME = N'D:\Data\Data_Source_2007.mdf' , SIZE = 51200KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2007]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2008Data', FILENAME = N'D:\Data\Data_Source_2008.mdf' , SIZE = 51200KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2008]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2009Data', FILENAME = N'D:\Data\Data_Source_2009.mdf' , SIZE = 51200KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2009]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2010Data', FILENAME = N'D:\Data\Data_Source_2010.mdf' , SIZE = 512KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2010]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2011Data', FILENAME = N'D:\Data\Data_Source_2011.mdf' , SIZE = 512KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2011]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2012Data', FILENAME = N'D:\Data\Data_Source_2012.mdf' , SIZE = 512KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2012]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2013Data', FILENAME = N'D:\Data\Data_Source_2013.mdf' , SIZE = 512KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2013]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2014Data', FILENAME = N'D:\Data\Data_Source_2014.mdf' , SIZE = 512KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2014]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2015Data', FILENAME = N'D:\Data\Data_Source_2015.mdf' , SIZE = 512KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2015]
GO

4) Create a partition function.
This is used to define data ranges that partitioning needs to take account of >

CREATE PARTITION FUNCTION [PFunction_YearDateRange](datetime) AS
RANGE LEFT FOR VALUES ( N'20041231 23:59:59.997'  --between January 1st and Dec 31st 2005
, N'20051231 23:59:59.997'  --before January 1st and Dec 31st 2006
, N'20061231 23:59:59.997'  --before January 1st and Dec 31st 2007
, N'20071231 23:59:59.997'  --before January 1st and Dec 31st 2008
, N'20081231 23:59:59.997'  --before January 1st and Dec 31st 2009
, N'20091231 23:59:59.997'  --before January 1st and Dec 31st 2010
, N'20101231 23:59:59.997'  --before January 1st and Dec 31st 2011
, N'20111231 23:59:59.997'  --before January 1st and Dec 31st 2012
, N'20121231 23:59:59.997'  --before January 1st and Dec 31st 2013
, N'20131231 23:59:59.997'  --before January 1st and Dec 31st 2014
, N'20141231 23:59:59.997'  --after January 1st 2015
)
GO

5) Create a partition scheme.
This is used to associate the ranges defined in the partition function with scheme names. It determines which filegroup incoming data is placed in.

CREATE PARTITION SCHEME PScheme_YearDateRange
AS
PARTITION PFunction_YearDateRange TO
([2004],[2005],[2006],[2007],[2008],[2009],[2010],[2011],[2012],[2013],[2014],[2015])
GO

6) Create our new table to take advantage of partitioning.
Note : The table is created on the function which determines the filegroup, rather than on a filegroup itself. >

CREATE TABLE [dbo].[PartitionTestTable] (
[UniqueId] [bigint] IDENTITY(1,1) NOT NULL,
[EventDateTime] [datetime] NOT NULL,
[ArbitraryCode1] [varchar](5) NULL,
[ArbitraryCode2] [varchar](5) NULL,
[ArbitraryCode3] [varchar](5) NULL,
[UnicodeText1] [nvarchar](255) NULL,
[UnicodeText2] [nvarchar](255) NULL,
[UnicodeText3] [nvarchar](255) NULL,
[Value] [money] NULL,
[Status] [bit] NOT NULL)
ON PScheme_YearDateRange(EventDateTime)
GO

7) Populate the table with varied data to fulfill the requirements of testing the partitioning. In this case, use data in different years.

You're done !

If you're interested, this system query will tell you the row count in each partition.

SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='PartitionTestTable';

Similarly, when sufficient data is inserted, the size of the data files will differ in the operating system.

Partitioning gives you the option to place different filegroups in different physical locations e.g. faster disks for newer, more frequently accessed data.
It also makes archiving old data much easier.

links :
http://blog.sqlauthority.com/2008/01/25/sql-server-2005-database-table-partitioning-tutorial-how-to-horizontal-partition-database-table/
http://weblogs.sqlteam.com/dang/archive/2008/12/11/Partition-Details-and-Row-Counts.aspx