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. DROP THE FOREIGN KEY CONSTRAINTS on partitioned table

 2. DROP THE NON PARTITIONED INDEXES on partitioned table

3. Create the switched table PARTITION_TAB_SWTICHED, exactly as the Partitioned table including ansi_nulls, quoted_identifier,check constraints etc

 4. Find the partition number to switch

Suppose we are interested to update all data for July’2015

 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 to match the partitioned table

 9. SWITCH THE PARTITION BACK TO PARTITIONED TABLE

 10. CREATE THE NON PARTITIONED INDEXES on partitioned table

11. CREATE THE FOREIGN KEY CONSTRAINTS on partitioned table

 

 

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

*