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.

Question:

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?