BrainDonor (4/1/2015)
Matthew Darwin (4/1/2015)
Archiving and index maintenance are the two reasons I've been given, though I have an inkling the real reason might simply be wanting to try it out.However, I suspect that it may well be a sensible candidate as the old table has some 700 million rows, most of which now is unneeded data.
We use partition switching to keep data for a variety of time periods - from 3 days to 15 months, depending upon what we need it for.
You'd be better off having a partition scheme based on age, not the actual date, because you'll have to alter it as time progresses.
Have a look at an example of a three day retention setup,which can be changed to suit the retention you require - http://wp.me/p3Vxvi-6S
Changing a table with 700 million rows to cater for partitioning is going to be interesting.
Edited for typo.
Does that scheme not mean that data is actually required to be moved between partitions, rather than just the partitions themselves remaining static and then being binned off when that data counter hits a specific age?
The old table is not going to be migrated into the new table, this is essentially a new system that will be starting with no data and building up from scratch; however I just want to make sure that the decision already made aren't going to mean that this is a problem going forwards once the table has accumulated that sort of volume of data.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]