Order of DROP_EXISTING and ONLINE for CREATE INDEX

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 1

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.)