Table Partition Help

I am trying to understand how table partitioning works. I have Database with 5 Filegroups. Database contains one Table which is partitioned.

Filegroups
Primary
FG1 – used for Partition
FG4 – used for Partition
FG3 – used for Partition
FGINDEX - used for Index

There are no Primary Keys on my table but it does contain a clustered index (created on FGINDEX). When I insert records into my table, my partitioned Filegroup does not grow, however my Index filegroup does grows. However when I do a search on my partition it shows that my Partitioned Filegroupcontains data.

I have a feeling that all the data is being inserted in to my index filegroup (FGINDEX). Also if I change the alter the index into nonclusteredmy Data file group grows, however the Index filegroup is always bigger than the data filegroup. I am including the code for your review.

Thx

-- Create DB

USE [master]

GO

CREATEDATABASE [TestPart]ONPRIMARY

(NAME= N'TestPartPrimary',FILENAME= N'C:\TestPart_Primary.MDF',SIZE= 1024KB,MAXSIZE= 5120KB,FILEGROWTH= 1024KB),

FILEGROUP [FG1]

(NAME= N'FG1',FILENAME= N'C:\FG1.ndf',SIZE= 1024KB,MAXSIZE= 5120KB,FILEGROWTH= 1024KB),

FILEGROUP [FG2]

(NAME= N'FG2',FILENAME= N'C:\FG2.ndf',SIZE= 1024KB,MAXSIZE= 5120KB,FILEGROWTH= 1024KB),

FILEGROUP [FG3]

(NAME= N'FG3',FILENAME= N'C:\FG3.ndf',SIZE= 1024KB,MAXSIZE= 5120KB,FILEGROWTH= 1024KB),

FILEGROUP [FGINDEX]

(NAME= N'FGINDEX',FILENAME= N'C:\FGINDEX.ndf',SIZE= 1024KB,MAXSIZE= 5120KB,FILEGROWTH= 1024KB)

LOGON

(NAME= N'TestPart_Log',FILENAME= N'C:\TestPart_Log.LDF',SIZE= 2048KB,MAXSIZE= 10240KB,FILEGROWTH= 1024KB)

COLLATE Latin1_General_CI_AS

GO

-

-- Create PARTITION

CREATEPARTITIONFUNCTION myRangeF1(int)

ASRANGELEFTFORVALUES(50, 100)

CREATEPARTITIONSCHEME myRangeS1

ASPARTITION myRangeF1

TO(FG1, FG2, FG3)

-

-- Create Table

USE [TestPart]

GO

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

CREATETABLE [dbo].[My_tbl](

[ID] [int]NULL,

[myDate] [datetime]NULLCONSTRAINT [DF_My_tbl_myDate]DEFAULT(getdate()),

[PartID] [int]NULL

)ON [FGINDEX]

-

-- Create Index

USE [TestPart]

GO

CREATECLUSTEREDINDEX [IX_My_tbl_PartID]ON [dbo].[My_tbl]

([PartID]ASC)WITH

(PAD_INDEX=OFF, SORT_IN_TEMPDB=OFF, DROP_EXISTING=OFF, IGNORE_DUP_KEY=OFF, ONLINE=OFF)

ON [FGINDEX]

-

-- Insert Data

SETNOCOUNTON

declare @mcountasint

declare @mdateasdatetime

set @mcount= 1000000

set @mdate=getdate()

while @mcount> 0

begin

insertinto dbo.My_tblvalues(0,@mdate,0)

set @mcount= @mcount- 1

end

SETNOCOUNTOFF

-

-- Search PARTITION

SELECT$PARTITION.myRangeF1(ID)ASPartition,

COUNT(*)AS [COUNT]FROM My_Tbl

GROUPBY$PARTITION.myRangeF1(ID)

ORDERBYPartition

[23089 byte] By [Fawadb] at [2007-12-25]