Common SQL Server partition addition and deletion Algorithms

First, add a data table to a partition table:
[Dbo]. [SP_Helper_Partition_Add]
@ SrcTable nvarchar (256), table to be added
@ DestTable nvarchar (256), target table
@ IdxOnDest nvarchar (1024): index creation Statement on the target table
@ Partition_func_name nvarchar (256), Partition Function
@ PartCol nvarchar (256), partition Column
@ SonIsPart tinyint = 1, whether the table to be added is partitioned
The algorithm is as follows:
Obtains the value of the partition column in the source table;
Add the uniqueness restriction of partition column values to the source table;
Then, determine whether a partition can store the table. If it does not exist, return;
Create the same index as the target table on the source table;
Now, the source table and target table have the same structure. The content of the source table can exist in the target partition, And the alter table switch is used to complete the partition;
Algorithm used to delete a partition
[Dbo]. [SP_Helper_Partition_Delete]
@ SrcTable nvarchar (256): delete partitions from the table.
@ IdxOnTable nvarchar (1024), the index creation statement for this table
@ Part_func nvarchar (256), partition function name
@ PartCol nvarchar (256), partition Column
@ PartIndex int: Index of the partition to be deleted
@ IdxIsPart tinyint whether the index is partitioned on another Partition Function
The algorithm is as follows:
First, copy the table structure of a temporary table according to the table structure of the source table;
Obtains the Boundary Value of the partition to be deleted;
Create restrictions on temporary tables;
Create an index on the table;
Switches a specified partition to a temporary table using alter switch;
Delete temporary table