We have a existing table and it has two date columns (Datatype - Datetime) and it's very big table and we would like to make a partition table. we have sql 2005This is a frequently accessed table.We need partition for different date ranges.I am thinking following:

1)Create one or two new file group as we have only one PRIMARY FILE GROUP2) create the partition function for my DateTime Column3) create the partition scheme to link the partition function to the new filegroup

Now i have to DateTime Column - OpenDate and closeDate, I need to use following logic:

If Opendate > Today's date then i need to move into Partition1If Opendate = Today's date then i need to move into Partition2and for another columnIf Closedate < (Today's date - 24 hrs) then i need to move into another Partition3

partitioning is a good idea for large datasets. it's been discussed quite a lot here (i don't use it myself but search around)

you will however have to consider what to do with "todays data tomorrow". i suspect you're going to have to come up with some sort of archiving process to be run nightly.

normally partioning involves data being keyed into one partition and being left there forever. your requiement seems to want a "small tidy current" table linked to a "large slow growing old data" table.

Thanks Andrew and Jack.You both are right. I will be creating different File group on diff drives but my original logic was wrong.

Actually, We have changed the partition logic and it is:

We have three Database - SearchDb, MSDB1, MSDB2

SearchDB has table called - KeyTab and it has PK field called ID which has Identity and another column called AU_ID which is served asKey Field to join another table and another table has same field.We are planning to add new column called Group ID and make it as Partition Column, this group id will consist in a three Range/group1, 2, 3 which will we our partition.Once i add the column Group Id into table. I need to create the partition into this existing table.

1) I will add the three file group into existing table using following:

2) Create the partition Function

3) Create the Partition Scheme

Now i need to create the procedure to Insert the data into different Partition using following criteria:

Check the AU_ID into SearchDB.KeyTab and compare with MSDB1.AUSUMTab and if StartDate > today's Date then Insert into Partition 1 of SearchDB Database of Partitioned Table KeyTabif StartDate < today's Date then Insert into Partition 2 of SearchDB Database of Partitioned Table KeyTaband another logic for EndDate i need to add asif EndDate < today's Date then Insert into Partition 3 of SearchDB Database of Partitioned Table KeyTab

Could you please guide me that my above steps are right and also how i can write the Procedure to Insert data into Partition?