Question :
If I call a CREATE NONCLUSTERED INDEX using DROP_EXISTING = ON and ONLINE = ON what order are things done it?
What I want is:
- New index is made in the TempDB
- Existing Index is dropped
- New index is put in its place.
But I worry that it is like this:
- Existing Index is dropped
- New index is made in the TempDB
- New index is put in its place.
Basically I am wondering if the old index is gone while the new index is being made? (I want it to be there to limit the time when I am running without any index.)
If it is a long time frame then I will make a new index with a different name then drop the existing one.
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.)