Have you ever wondered whether it is faster to drop and re-create an index or to use the ALTER INDEX REBUILD statement in SQL Server? This is a common question among database administrators and developers. In this article, we will explore this topic and provide some insights based on a series of tests.

Before we dive into the tests, let’s briefly explain the difference between dropping and re-creating an index and using the ALTER INDEX REBUILD statement. When you drop and re-create an index, you essentially remove the existing index and create a new one from scratch. On the other hand, the ALTER INDEX REBUILD statement rebuilds the index without dropping it, which can be more efficient in certain scenarios.

To conduct our tests, we will focus on both clustered indexes (CI) and non-clustered indexes (NCI). We will also test tables with a single index and tables with multiple indexes. For our test data, we will use a copy of the Sales.SalesOrderDetail table from the AdventureWorks2014 database.

Clustered Index Test

In our first test, we will compare the performance of dropping and re-creating a clustered index versus using the ALTER INDEX REBUILD statement on a table with a single index. Here is the code for the test:

SELECT * INTO IndexTest
FROM AdventureWorks2014.Sales.SalesOrderDetail

CREATE CLUSTERED INDEX ci_Test ON IndexTest(ProductID, CarrierTrackingNumber)

-- DROP and CREATE
DROP INDEX IndexTest.ci_Test;
CREATE CLUSTERED INDEX ci_Test ON IndexTest(ProductID, CarrierTrackingNumber);

-- ALTER INDEX REBUILD
ALTER INDEX ci_Test ON IndexTest REBUILD;

After running the test multiple times, we found that there was no significant difference in performance between dropping and re-creating the index and using the ALTER INDEX REBUILD statement.

Non-Clustered Index Test

In our second test, we will compare the performance of dropping and re-creating a non-clustered index versus using the ALTER INDEX REBUILD statement on a table with a single index. Here is the code for the test:

DROP INDEX IndexTest.ci_Test
CREATE NONCLUSTERED INDEX nci_Test ON IndexTest(ProductID, CarrierTrackingNumber)

-- DROP and CREATE
DROP INDEX IndexTest.nci_Test;
CREATE NONCLUSTERED INDEX nci_Test ON IndexTest(ProductID, CarrierTrackingNumber);

-- ALTER INDEX REBUILD
ALTER INDEX nci_Test ON IndexTest REBUILD;

Interestingly, we observed a significant difference in performance between dropping and re-creating the non-clustered index and using the ALTER INDEX REBUILD statement. The latter consistently outperformed the former, with lower elapsed time and IO.

Clustered Index Test with Multiple Indexes

In our third test, we will compare the performance of dropping and re-creating a clustered index versus using the ALTER INDEX REBUILD statement on a table with multiple indexes. Here is the code for the test:

CREATE CLUSTERED INDEX ci_Test ON IndexTest(ProductID, CarrierTrackingNumber)

-- DROP and CREATE
DROP INDEX IndexTest.ci_Test;
CREATE CLUSTERED INDEX ci_Test ON IndexTest(ProductID, CarrierTrackingNumber);

-- ALTER INDEX REBUILD
ALTER INDEX ci_Test ON IndexTest REBUILD;

Surprisingly, we found a significant difference in performance between dropping and re-creating the clustered index and using the ALTER INDEX REBUILD statement. The latter consistently outperformed the former, with lower elapsed time and IO. This can be attributed to the fact that when you drop or add a clustered index, all non-clustered indexes need to be rebuilt as well.

Conclusion

Based on our tests, it appears that using the ALTER INDEX REBUILD statement is generally the better approach in terms of performance. It consistently showed lower IO and frequently better elapsed time compared to dropping and re-creating indexes. However, it is always recommended to run your own tests and consider your specific requirements and circumstances.

Thank you for reading this article on SQL Server index management. We hope you found it informative and helpful. Stay tuned for more SQL Server tips and tricks!