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 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 filegroupNow 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 Partition3Could 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 drivesJack 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, MSDB2SearchDB 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 Function3) Create the Partition SchemeNow 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 KeyTabCould 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 |
|
|
|
|
|
|
|
|
Partition to existing sql server table