Assuming these are non clustered indexes then the CREATE INDEX
topic on BOL discussing offline operations indicates this approach should only partially work in that it will allow read only access to the table but will still block updates. The relevant section is below (emphasis mine)
Table locks are applied for the duration of the index operation. An
offline index operation that creates, rebuilds, or drops a clustered
index, or rebuilds or drops a nonclustered index, acquires a Schema
modification (Sch-M) lock on the table. This prevents all user access
to the underlying table for the duration of the operation. An offline
index operation that creates a nonclustered index acquires a Shared
(S) lock on the table. This prevents updates to the underlying table
but allows read operations, such as SELECT statements.
Now, the best way to answer this is to set up a smaller test database of a few gigabytes. It will accurately predict the best technique.
Your technique (2) is horrible because step 1 leads to all NCI's being rebuilt to work together with the resulting heap table. Don't do that.
The good news is that DROP_EXISTING
works with changing the index definition including columns and partition scheme:
The existing index is dropped and rebuilt. The index name specified
must be the same as a currently existing index; however, the index
definition can be modified. For example, you can specify different
columns, sort order, partition scheme, or index options.
And this is the reason why this feature exists. It is not equivalent to first dropping, then creating.
Here are the basic techniques (all of yours are horrible due to the intermediate heap table):
- Drop all NCIs, rebuild the CI
WITH (DROP_EXISTING = ON)
, create missing NCIs
- Drop all non-aligned NCIs, rebuild the CI
WITH (DROP_EXISTING = ON)
, create missing NCIs
- Rebuild the CI
WITH (DROP_EXISTING = ON)
(1) is very light on the transaction log. It is not maximally efficient. (3) is the simplest - it will cause all indexes to be rebuild as aligned indexes. This is not so good because you will have to rebuild those that you don't want to align.
(2) is probably best in all cases. I can't think of any case where it might be worse-off. It has the advantage of reusing existing indexes for a more efficient rebuild (this is possible because you didn't just drop all NCIs). NCIs which you want to be non-aligned don't benefit from this method and would be rebuild needlessly. So get rid of them before starting the procedure.
All of this actually works online although you said you don't need that.
Again, just try it out on a sample database. Certainly you shouldn't entrust the availability of your database to my advice. I suggest you take the ideas from this post and try them out.
Best Answer
The old index stays in place, is maintained and being used by queries. Only at the end of the index build does SQL Server alter the metadata in such a way that the old index disappears and the new one is set in.
There is not a single millisecond without an index. (But there is a short-lived Sch-M lock twice during the online build.)