SQL Interview: 37 DROP CREATE vs DISABLE REBUILD for SQL Server indexes

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Advanced

Question:

You need to write code to avoid the impact of indexes during data loads.

You could DROP and CREATE the indexes, or you could DISABLE and REBUILD them.

Which should you choose and why? How would the performance compare between the two options?

Answer:

When you use DROP and CREATE, you need to ensure that you know exactly how the index was implemented before the DROP, to make sure you put it back the right way during the CREATE. That requires you to either have an accurate up-to-date script, or to script out the index before dropping it.

By using DISABLE on the index, the metadata is retained for you. Then you can later REBUILD the index without needing to specify how it must be created.

The performance of each method would be so close as to be irrelevant.

2025-04-15