Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Partition to existing sql server table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

105 Posts

Posted - 01/25/2013 :  00:57:03  Show Profile  Reply with Quote
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 2005
This 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 GROUP
2) create the partition function for my DateTime Column
3) 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 Partition1
If Opendate = Today's date then i need to move into Partition2
and for another column
If Closedate < (Today's date - 24 hrs) then i need to move into another Partition3

Could you please suggest, how i can handle it?

Flowing Fount of Yak Knowledge

2916 Posts

Posted - 01/25/2013 :  11:59:55  Show Profile  Reply with Quote
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.
Go to Top of Page

Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 01/26/2013 :  05:46:26  Show Profile  Visit jackv's Homepage  Reply with Quote
When organing the location of the filegroups - consider optimising by placing on different drives

Jack Vamvas
Go to Top of Page

Posting Yak Master

105 Posts

Posted - 01/27/2013 :  09:42:58  Show Profile  Reply with Quote
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 as
Key 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/group
1, 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 KeyTab
if StartDate < today's Date then Insert into Partition 2 of SearchDB Database of Partitioned Table KeyTab
and another logic for EndDate i need to add as
if 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?

Go to Top of Page

Posting Yak Master

105 Posts

Posted - 01/30/2013 :  15:59:16  Show Profile  Reply with Quote
Any help?

Go to Top of Page

Posting Yak Master

160 Posts

Posted - 02/08/2013 :  18:48:24  Show Profile  Reply with Quote
Simple, create an Index on Partition scheme when you are done with updating table with new column, partition function and partition scheme. All the non partitioned data will move to partitioned table
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000