Thanks everyone for the replies. Greatly appreciated
I have a couple of more questions on partitioning:
This is the system that I have developed and been testing.
SPLIT -- SWITCH -- MERGE
SPLIT - Create a stored procedure that selects the split date = to the first day of the month
and splits the archive table at that date.
SWITCH - Create a stored procedure to switch the oldest partition with the empty new partition that was created
using the previous procedure
MERGE - Create a stored procedure to merge the empty in the original table so as to remove that partition and
So basically a new empty partition will be created in the archive table. And the oldest partition in the live table will be switched out with the empty partition in the archive table. Then the oldest partition in the live table will be removed by MERGE(ing) the boundary.
I have done several tests and it seems to work.
1. I see alot of case studies using staging tables, but I will not be using that. The oldest data is being switched out
with a new empty partition created in the archive table
Both the archive and live table have the same table structured, indexes etc
Is a staging table necessary?