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 |
|
stapsey
Starting Member
3 Posts |
 Posted - 2008-03-09 : 07:15:00
|
| I have a requirement that I need to reload the last seven days worth of data each night to ensure that we pick up late arriving and updated records. To avoid having to do updates we delete the last seven days data and reload. I was wondering if it is possible to set up the table as a partition, paritioned on a value (OLD, NEW) or similar.The job would set the last day in the NEW partition to be old, the theory being that this would cause the rows to move to the OLD parition, and then truncate the new partion rather than deleting. The last seven days data could then be inserted into the empty new partition.My questions is 1. Is my theory about the data moving from one partition to another correct. 2. Can I actually truncate and individual parition, 3. Do you think it will perform any quicker. We would expect data in the range of 100K to 500K rows in the seven days and will store up to 4 years of historical data.Thanks for your thoughtsStapsey |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-09 : 10:46:18
|
| Yes, you can switch out old partition. But i do manually instead of setting as a job.Performance will be really faster if you have multiple drives to spread partition. Check switch in/outmove out in Table BOL. |
 |
|
|
|
|
|
Data Moving between partitions