1

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.

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.