1

We have functionality in SQL Server to rebuild indexes at partition level using below command

ALTER INDEX IX_name ON table REBUILD PARTITION=68

In non-partitioned table we disable the indexes, load the data and rebuild the indexes after the data load complete. Here we disable the indexes prior to data load so that indexes will not be processed while data insertion to table is happening.

How can I do the same process for a partitioned table? If i disable indexes on a partitioned table before data load, and run the rebuild partition command after the data load is complete, it won't work, saying disabled indexes cannot be rebuilt.

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Enterprise Edition.

Improve this question
0

1 Answer 1

Reset to default
1

How can I do the same process for a partitioned table?

While you can rebuild a single partition, you can't disable just one partition.

So you would use a staging table on the same partition scheme. Disable its indexes, load it, rebuild the indexes, and then switch one or more partitions into the main table.

Improve this answer
2
  • thanks @David Browne - Microsoft..I am aware that we cannot disable single partition in a table, i was trying to know if there is any possibility that we can stop indexes to process while insert/update on table without disabling indexes ,so i can rebuild the partitions that i loaded with new data alone.
    – Rsola
    Commented Nov 1, 2018 at 22:12
  • The only way to "rebuild the partitions that i loaded with new data alone" is to use a staging table and partition switching.
    – David Browne - Microsoft
    Commented Nov 1, 2018 at 22:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.