Is it possivle to drop and index, and rebuild it, instead of disable it and rebuild?
I agree that "correct answer" isn't correct.
rebuild indexes that have been dropped, you would need to
create them again, and it would make no sense to do the insert last after the indexes have been dropped and created anyway.
I presume the correct answer is in fact something like
- Disable the non clustered indexes.
- Do the Insert
- Rebuild the non clustered indexes.
As this is a heap this could be implemented with
ALTER INDEX ALL ON OrdersHistory DISABLE
ALTER INDEX ALL ON OrdersHistory REBUILD
Which has zero impact on existing permissions, and low administrative effort compared with other possible alternatives such as the solution you propose of dropping and recreating all indexes.
The proposed solution doesn't make much sense because:
ALTER INDEX ALLon a heap has no effect; for heaps, it only affects the NonClustered Indexes, none of which exist any longer in the proposed solution. And even if this Table had a Clustered Index that was dropped in the step to drop all indexes, it would then be a heap. As the MSDN page for ALTER INDEX states:
If ALL is specified and the underlying table is a heap, the rebuild operation has no effect on the table. Any nonclustered indexes associated with the table are rebuilt.
In this scenario, the
ALTER INDEX ALL ... REBUILDoperation would do absolutely nothing, yet it also would not throw an error.
If the intent of the
ALTER INDEX ALL ... REBUILDis to release unused datapages (which might be needed due to this table being a heap), then it still doesn't make sense because that only happens with
ALTER TABLE ... REBUILD(I tested both commands).
The Indexes are never recreated. This seems a bit irresponsible ;-)
Everything that @Martin mentioned in his answer
With respect to the requirement of
minimal administrative effort, @Martin's answer covers the operational aspect of this, but another aspect is that it can be accomplished with more granular permissions than are required to drop and create indexes. According to the MSDN page for
ALTER INDEX(linked above):
To execute ALTER INDEX, at a minimum, ALTER permission on the table or view is required.
Essentially, the proposed solution is to:
- Possibly require additional permissions
- Do a bunch of work (i.e. dropping the Indexes)
- Execute a command to do nothing (i.e. the
ALTER INDEX ALL ... REBUILD)
- Walk away, leaving others to waste time looking for the reason that queries are running slower than usual, fully expecting those Indexes to be in place and so possibly not the first place they might check. But even when they do figure it out, someone will still have to go find the most recent Index definitions for that table.
It is rather odd advice. I would stick with Martin's proposed answer ;-).