Is it possivle to drop and index, and rebuild it, instead of disable it and rebuild?


I agree that "correct answer" isn't correct.

You can't 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

  1. Disable the non clustered indexes.
  2. Do the Insert
  3. Rebuild the non clustered indexes.

As this is a heap this could be implemented with

  2. INSERT ...

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:

  1. Calling ALTER INDEX ALL on 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 ... REBUILD operation would do absolutely nothing, yet it also would not throw an error.

    If the intent of the ALTER INDEX ALL ... REBUILD is 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).

  2. The Indexes are never recreated. This seems a bit irresponsible ;-)

  3. Everything that @Martin mentioned in his answer

  4. 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:

  1. Possibly require additional permissions
  2. Do a bunch of work (i.e. dropping the Indexes)
  3. Execute a command to do nothing (i.e. the ALTER INDEX ALL ... REBUILD)
  4. 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 ;-).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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