Order of DROP_EXISTING and ONLINE for CREATE INDEX

Posted on

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

Leave a Reply

Your email address will not be published. Required fields are marked *