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

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Partition to existing sql server table

Author  Topic 

poratips
Posting Yak Master

105 Posts

Posted - 2013-01-25 : 00:57:03
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?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2013-01-25 : 11:59:55
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

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-01-26 : 05:46:26
When organing the location of the filegroups - consider optimising by placing on different drives

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

poratips
Posting Yak Master

105 Posts

Posted - 2013-01-27 : 09:42:58
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?

Thanks,
Go to Top of Page

poratips
Posting Yak Master

105 Posts

Posted - 2013-01-30 : 15:59:16
Any help?

Thanks.
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 2013-02-08 : 18:48:24
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
   

- Advertisement -