Partition switch : A high performance strategy for data update, delete, insert

by Snehashish Ghosh
STEPS

1. DROP THE FOREIGN KEY CONSTRAINTS on partitioned table
2. DROP THE NON PARTITIONED INDEXES on partitioned table
3. Create the switched table TABLE_NAME_SWTICHED, exactly as the Partitioned table including ansi_nulls, quoted_identifier,check constraints etc
4. Find the partition number to switch
5. Perform partition switch to switched table
6. optional : CREATE, DROP non clustered index to improve query performance on the switched table
7. Perform the data update on switched table
8. CREATE the dropped non clustered indexes on the switched table to match the partitioned table
9. SWITCH THE PARTITION BACK TO PARTITIONED TABLE list of indexes
10. CREATE THE NON PARTITIONED INDEXES on partitioned table
11. CREATE THE FOREIGN KEY CONSTRAINTS on partitioned table
 

1...

Read More