I saw someone ask a few weeks ago if it was faster to drop and re-create an index or do an ALTER INDEX REBUILD. I’m not sure, although I suspect they will take about the same amount of time. And what do I do when I’m not sure how something is going to work? I try it out!

The hardest part of this is figuring out what exactly to test. In order to do a somewhat complete test I want to test both clustered indexes (CI) and non-clustered indexes (NCI). However I also need to test a table with a single index (CI vs NCI) and one with multiple indexes running a separate set of tests on both the CI and NCI indexes. I don’t feel like the fragmentation level will make any difference and REBUILD ONLINE doesn’t have the same functionality as DROP and CREATE.

I’m going to use a copy of Sales.SalesOrderDetail and build new indexes on ProductId int and CarrierTrackingNumber nvachar(50) to get a reasonable sized index.

Initial Setup

SELECT * INTO IndexTest
FROM AdventureWorks2014.Sales.SalesOrderDetail
GO

---

Clustered Index Test Single Index

Setup

CREATE CLUSTERED INDEX ci_Test ON IndexTest(ProductID, CarrierTrackingNumber)
GO

DROP and CREATE

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
DROP INDEX IndexTest.ci_Test;
GO
CREATE CLUSTERED INDEX ci_Test ON IndexTest(ProductID, CarrierTrackingNumber);
GO

ALTER INDEX REBUILD

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
ALTER INDEX ci_Test ON IndexTest REBUILD;
GO

Results

-- DROP and CREATE
Table 'IndexTest'. Scan count 3, logical reads 1566, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 281 ms,  elapsed time = 680 ms.
   
-- ALTER INDEX REBUILD
Table 'IndexTest'. Scan count 2, logical reads 1580, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 157 ms,  elapsed time = 581 ms.

No major differences here. To be honest I ran this code over and over again and kept coming up with different times (IO stayed the same of course). Over all it looked to close to call.

---

Non Clustered Index Test Single Index

Setup

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

DROP and CREATE

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
DROP INDEX IndexTest.nci_Test;
GO
CREATE NONCLUSTERED INDEX nci_Test ON IndexTest(ProductID, CarrierTrackingNumber);
GO

ALTER INDEX REBUILD

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
ALTER INDEX nci_Test ON IndexTest REBUILD;
GO

Results

-- DROP and CREATE
Table 'IndexTest'. Scan count 3, logical reads 1566, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 218 ms,  elapsed time = 194 ms.
   
-- ALTER INDEX REBUILD
Table 'IndexTest'. Scan count 2, logical reads 494, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 110 ms,  elapsed time = 98 ms.

Hmm, non clustered indexes showed a big difference here. Elapsed time was consistently about half for the REBUILD and IO is around 1/3.

---

Clustered Index Test Multiple Index

Setup

CREATE CLUSTERED INDEX ci_Test ON IndexTest(ProductID, CarrierTrackingNumber)
GO

DROP and CREATE

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
DROP INDEX IndexTest.ci_Test;
GO
CREATE CLUSTERED INDEX ci_Test ON IndexTest(ProductID, CarrierTrackingNumber);
GO

ALTER INDEX REBUILD

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
ALTER INDEX ci_Test ON IndexTest REBUILD;
GO

Results

-- DROP and CREATE
Table 'IndexTest'. Scan count 3, logical reads 1566, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 253 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 'IndexTest'. Scan count 3, logical reads 1566, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'IndexTest'. Scan count 2, logical reads 1584, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 484 ms,  elapsed time = 1104 ms.
   
-- ALTER INDEX REBUILD
Table 'IndexTest'. Scan count 2, logical reads 1584, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 188 ms,  elapsed time = 395 ms.

Wow, BIG difference here. Not really surprising when you think about it though. When you drop or add a clustered index all of the non-clustered indexes have to be rebuilt. This is because the clustered index columns are in each non clustered index. (Read here and here.) Since the rebuild doesn’t require changing the clustered index this doesn’t apply. Of course if rebuilding all of your indexes is your goal then this isn’t a huge issue. Of course there is an ALL option on ALTER INDEX REBUILD if that’s what you need.

---

Non Clustered Index Test Multiple Index

Setup

-- No setup required as both indexes are already in place.

DROP and CREATE

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
DROP INDEX IndexTest.nci_Test;
GO
CREATE NONCLUSTERED INDEX nci_Test ON IndexTest(ProductID, CarrierTrackingNumber);
GO

ALTER INDEX REBUILD

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
ALTER INDEX nci_Test ON IndexTest REBUILD;
GO

Results

-- DROP and CREATE
Table 'IndexTest'. Scan count 2, logical reads 1584, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 156 ms,  elapsed time = 182 ms.
      
-- ALTER INDEX REBUILD
Table 'IndexTest'. Scan count 1, logical reads 441, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 110 ms,  elapsed time = 185 ms.

No difference in time here but big difference in IO. This is important since with larger tables or slower IO subsystems the IO difference is likely to make a time difference also.

---

Summary

Over all it looks like ALTER INDEX REBUILD is the better way to go. With consistently lower IO and frequently better time (or at worst similar times) it appears to be the better way to go. Of course as always this is what I’m seeing and it’s a good idea to run your own tests. Your mileage may vary.