Could you please help me with a solution for dynamic partitioning in SQL Server including the creation of partition, splitting , Merging , Switching and archiving to the history table .
1.maintain 3 months in the current table .
2.older than 3 months , create,split,merge and switch the partition (partition switch in/switch out)
3. Move to history table older than 3 months
4. Archive and purge the data older than 5 years
All these steps should be performed dynamically in SQL SERVER. Please help me with the solution with an example source code. Your help is greatly appreciated.
Microsoft SQL Server 2005Microsoft SQL Server 2008