Hi Angelo,

Adding and removing a clustered index is the simplest, that is true. That does not automatically make it the best, and it will definitely not yield the lowest amount of down time automatically.

Remember that nonclustered indexes include either a reference to the clustered index if there is one, or a reference to the database page otherwise. Adding a clustered index forces each nonclustered index (one in the case of this example) to be rebuilt. And that goes for removing a clustered index as well.

Let's compare two approaches: the copy/drop/rename approach and the create/drop clustered index approach.

Create/drop clustered index

Creating the clustered index involves the following steps:

* Sort data (one million rows!)

* Copy data to new data pages

* Free previously allocated data pages

* Rebuild one nonclustered index

Dropping the clustered index is cheaper:

* Drop the index (metadata only)

* Rebuild one nonclustered index

Copy/drop/rename table

Copying the data to a new table:

* Copy data to new data pages

* Build one nonclustered index

Drop old table:

* Drop table and index (metadata only)

* Free previously allocated data pages (for index as well)

Rename table:

* Metadata only

As you see, adding and removing a clustered index causes the nonclustered index to be rebuilt twice, whereas the copy/drop/rename scenarion has to build the new nonclustered only once. This can add up significantly if the table has several nonclustered indexes. Other than that, the create/drop clustered index method also incurs a sort operation that is not really required.

I think the "best" option depends. How afraid are you of data loss due to operator error? How many nonclustered indexes are involved? How long is the time window available for the operation? If you need 24x7 availability and you have enterprise edition, you can use its online index rebuilding capabilities (though I'd have to check if they work for creating and dropping a clustered index as well) Etc, etc, etc.