I am adding a clustered index as an online operation to a table that has 3 billion rows. It has taken over 17 hours so far. If I do this again on another table with just as many rows should I first drop the nonclustered indexes?
Sql-server – Adding a Clustered index, should I drop the nonclustered indexes
sql serversql server 2014
Related Question
- Sql-server – Should I remove this clustered index
- Sql-server – Nonclustered index storage on clustered columnstore
- Sql-server – Recreating Clustered Index Efficiently
- Sql-server – Creating a clustered index and want to add log files
- Sql-server – Clustered Index Maintenance vs. Nonclustered Index Maintenance
- Sql-server – Why Do My Nonclustered Indexes Use More Space When I Delete Rows
- Sql-server – ny reason to have nonclustered indexes on a table with only 1 page in the clustered index
- Sql-server – What should be the size of nonclustered index
Best Answer
Dropping the nonclustered indexes will make converting the heap to a clustered index faster since those nonclustered indexes will not need to be updated.
However, you probably will want to recreate them after the conversion process is complete, so make sure you script the
CREATE INDEXscripts prior to dropping them.Be aware, dropping the nonclustered indexes means any queries that run during the conversion may run very slowly since they may be expecting indexes that are no longer there.