Bookmark : Automating Sliding Window Table Partitioning

This article is entitled 'Automating Sliding Window Table Partitioning' and describes 1 method (using SSIS) to implement horizontal partitioning on the fly.

I think it would need further work in terms of partition file placement and carries a small security consideration in that the package would need sufficient permissions to run the ALTER DATABASE and ALTER PARTITION SCHEME statements.

Still a good method and article though...

Link : Automatiing Sliding Window Table Partitioning

Demystified : How SQL Server uses threads

Data files & threads - Clarification (From Steve Jones' blog).

"The bottom line is that SQL Server uses a thread for each unique disk drive, NOT files. In SQL 2000, you can fool the system if the disk drives are volumes on the same physical disk. However in SQL 2005 the system checks to see if these are the same physical disk.

There are some caveats, but if you hear this rumor, dispel it. Let people know that multiple file groups (or files) only help if you have different physical I/O paths and drives."

http://www.sqlservercentral.com/blogs/steve_jones/archive/2009/10/13/sql-server-legend-data-files-and-threads.aspx

Therefore performance is only a reason for partitioning when separate disks are involved.
Obviously, partitioning is still worth the effort if you wish to reduce backup sizes & times by implementing a strategy that includes it.

Also, if data is partitioned and then your boss stumps up for another drive, you're ready to move some partitions!

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

Unique index on a Partitioned table

Partition columns for a unique index must be a subset of the index key >

ALTER TABLE Data.Event

ADD CONSTRAINT UniqueReference

UNIQUE(EventDateTime,EventRef)

http://databases.about.com/od/sqlserver/a/unique.htm
http://www.simple-talk.com/sql/database-administration/partitioned-tables-in-sql-server-2005/
http://msdn.microsoft.com/en-us/library/ms190273.aspx