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.
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. |
 |
|
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 |
 |
|
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, |
 |
|
poratips
Posting Yak Master
105 Posts |
Posted - 2013-01-30 : 15:59:16
|
Any help?
Thanks. |
 |
|
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 |
 |
|
|
|
|