Partitioning Question

I have a question about Partitions and if this is the best approach for my situation.

I have a table with many rows (300 Million or more) where there is a column that represents a date (actually it is an integer in the form of YYYYMM - example 200501 - January of 2005)

The data prior to a specific date (in my case 3 full years before the current YYYYMM - at this time 200501) is data that never changes. The data at that date (200501) and on is data that changes every month.

I have approximately 200 Million rows that are static (and growing every year) and 100 million that changes every month.

I wanted to do the following (and keep in mind i have to do a delete from this table every month and an insert every month)

1. Create a simple partition function like this to represent 2 buckets for data (one prior to 200501 and one post 200501)


CREATE PARTITION FUNCTION partPeriodData (INT)

AS RANGE RIGHT FOR VALUES (200501)

2. Create a partition scheme as follows (to separate the data from the two buckets onto two separate file groups)


CREATE PARTITION SCHEME partPeriodScheme AS

PARTITION partPeriodData TO

(ETOFinalHistoricalFG, ETOFinaCurrentlFG);

3. I Created my database as follows


CREATE DATABASE [Final] ON PRIMARY

( NAME = N'ETOFinalPrimary', FILENAME = N'd:DataETOFinalPrimary.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),

FILEGROUP [ETOFinaCurrentlFG]

( NAME = N'ETOFinalCurrent', FILENAME = N'd:DataETOFinalCurrent.ndf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),

FILEGROUP [ETOFinalHistoricalFG]

( NAME = N'ETOFinalHistorical', FILENAME = N'd:DataETOFinalHistorical.ndf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )

LOG ON

( NAME = N'ETOFinalLog', FILENAME = N'd:dataETOFinalLog.ldf' , SIZE = 102400KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )

GO

4. I Created my table as follows


CREATE TABLE [dbo].[f_eurostatdata](

[LoadId] [int] IDENTITY(1,1) NOT NULL,

[DeclarantId] [int] NULL,

[Partner] [int] NULL,

[ProductId] [int] NULL,

[Flow] [int] NULL,

[Stat_Regime] [int] NULL,

[Period] [int] NULL,

[Value1000ECU] [numeric](34, 4) NULL,

[QuantityTon] [bigint] NULL,

[SupQty] [numeric](34, 4) NULL,

[DateCreated] [datetime] NULL CONSTRAINT [DF_f_eurostatdata_DateCreated] DEFAULT (getdate()),

CONSTRAINT [PK_f_eurostatdata] PRIMARY KEY CLUSTERED

(

[LoadId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON partPeriodScheme(Period)

When i create my table i get the following message


The filegroup 'PRIMARY' specified for the clustered index 'PK_f_eurostatdata' was used for table 'dbo.f_eurostatdata' even though partition scheme 'partPeriodScheme' is specified for it.


Am i doing this right?

Corey