Implementation SQL Server 2005 Partition Table

A couple month ago, i have implemented SQL Server 2005 at my project using partition table. I saw this feature on the book and i thought it could have done increase the performance. The first thing i could have done at the time was searching the article about partition table on SQL Server 2005. Many article said that partition table was being used for archiving, for example archive transaction per date, per month and etc. But i had a different things to implemented the partition table. i chose to archive the transaction per id, means the primary key of table.

I analyzed the database design and made the partition function and scheme, which table can be partition. Before that, i have design the filegroup to fit the partition scheme. these are the filegroup example:

/* Filegroup Data */
FILEGROUP FG_BILLINGS_01
(
    NAME = 'FG_BILLINGS_01_01',
    FILENAME = 'E:\FILEGROUP\FG_BILLINGS_01_01.ndf',
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
),
(
    NAME = 'FG_BILLINGS_01_02',
    FILENAME = 'E:\FILEGROUP\FG_BILLINGS_01_02.ndf',
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
),
FILEGROUP FG_BILLINGS_02
(
    NAME = 'FG_BILLINGS_02_01',
    FILENAME = 'E:\FILEGROUP\FG_BILLINGS_02_01.ndf',
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
),
(
    NAME = 'FG_BILLINGS_02_02',
    FILENAME = 'E:\FILEGROUP\FG_BILLINGS_02_02.ndf',
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
),
FILEGROUP FG_BILLINGS_03
(
    NAME = 'FG_BILLINGS_03_01',
    FILENAME = 'E:\FILEGROUP\FG_BILLINGS_03_01.ndf',
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
),
(
    NAME = 'FG_BILLINGS_03_02',
    FILENAME = 'E:\FILEGROUP\FG_BILLINGS_03_02.ndf',
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
),
FILEGROUP FG_BILLINGS_04
(
    NAME = 'FG_BILLINGS_04_01',
    FILENAME = 'E:\FILEGROUP\FG_BILLINGS_04_01.ndf',
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
),
(
    NAME = 'FG_BILLINGS_04_02',
    FILENAME = 'E:\FILEGROUP\FG_BILLINGS_04_02.ndf',
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
),

/* Filegroup Index Data */
FILEGROUP FG_BILLINGSIdx
(
    NAME = 'FG_BILLINGSIdx_01',
    FILENAME = 'E:\FILEGROUP\FG_BILLINGSIdx_01.ndf',
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
),
(
    NAME = 'FG_BILLINGSIdx_02',
    FILENAME = 'E:\FILEGROUP\FG_BILLINGSIdx_02.ndf',
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
),

And these are the partition function and scheme:

CREATE PARTITION FUNCTION BillingsPFN(Numeric(29,0))
As
RANGE LEFT FOR VALUES
(
    42876, -- 2005
    64443, -- 2006
    78142 -- 2007
)
GO

CREATE PARTITION SCHEME BillingsPScheme
As
PARTITION BillingsPFN TO
(
    [FG_BILLINGS_01],
    [FG_BILLINGS_02],
    [FG_BILLINGS_03],
    [FG_BILLINGS_04]
)
GO

The partition function have values 42876,6443,78142. These values are archiving scheme using ID (primary key or foreign key) per year. Thus, every year, they have to be maintained with adding new filegroup. The partition table has feature Split and Merge, My plans are after the new filegroup is built, the old filegroup can be merge (to maintain filegroup always have 4 files). After i created these method, it had to applied to underlying table. Here it is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BILLINGS]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BILLINGS](
    [billing_id] [dbo].[Number_Sequence] NOT NULL,
    [billing_date] [datetime] NULL,
    [payment_schedule_line_id] [dbo].[Number_Sequence] NULL,
    [income] [dbo].[Number_Currency] NULL,
    [created_date] [datetime] NULL,
    [principal] [dbo].[Number_Currency] NULL,
    [penalty] [dbo].[Number_Currency] NULL,
    [contract_id] [dbo].[Number_Sequence] NOT NULL,
    [delete_flag] [dbo].[String_Flag] NULL,
    [overdue_days] [dbo].[Number_Sequence] NULL,
    [overdue_days_penalty] [dbo].[Number_Sequence] NULL,
CONSTRAINT [PK_BILLINGS] PRIMARY KEY CLUSTERED
(
    [billing_id] ASC,
    [contract_id] ASC
)WITH (PAD_INDEX = ON, FILLFACTOR = 90) ON BillingsPScheme (contract_id)
) ON BillingsPScheme (contract_id)
END
GO

SET ANSI_PADDING OFF
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[BILLINGS]') AND name = N'IX_BILLINGS_1')
CREATE NONCLUSTERED INDEX [IX_BILLINGS_1] ON [dbo].[BILLINGS]
(
    [contract_id] ASC,
    [payment_schedule_line_id] ASC,
    [delete_flag] ASC,
    [billing_date] ASC
)WITH (PAD_INDEX = ON, FILLFACTOR = 40) ON BillingsPScheme (contract_id)
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[BILLINGS]') AND name = N'IX_BILLINGS_2')
CREATE NONCLUSTERED INDEX [IX_BILLINGS_2] ON [dbo].[BILLINGS]
(
    [contract_id] ASC,
    [payment_schedule_line_id] ASC,
    [created_date] ASC,
    [delete_flag] ASC,
    [billing_date] ASC
)WITH (PAD_INDEX = ON, FILLFACTOR = 40) ON BillingsPScheme (contract_id)
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[BILLINGS]') AND name = N'IX_BILLINGS_3')
CREATE NONCLUSTERED INDEX [IX_BILLINGS_3] ON [dbo].[BILLINGS]
(
    [payment_schedule_line_id] ASC,
    [created_date] DESC,
    [delete_flag] ASC
)WITH (PAD_INDEX = ON, FILLFACTOR = 40) ON [FG_BILLINGSidx]
GO

I have read that to get the best performance, use the partition scheme to put your index table when the partition values is specified. You can see the sample query on IX_BILLINGS_1 index.

These are the only sample from my project database structure, After i implemented all of these step for all. The next day, all user used this the new database environment. What is the result, that's suprising me because they can boost my database performance. End of Day process has less than one hour (After rebuild index - maintenance your database) from three hours. I'm glad for that. but i see the drawback, that is you can't set the foreign key within table partition references. it is caused the value of partition have to be primary key as well. Thus, if you want to migrate from old structure and the old structure still intact, you have to let it off the foreign key, Or you build the new structure (with partition table) and maybe there are the impact to your application, such as the query insert, query join etc.