0

I know that an online rebuild allows CRUD query to be executed while the index is rebuilding.

Assuming I am using sql 2017 standard edition, it doesn't support online index rebuild. I want to understand what I can expect in the following scenario:

  1. Yet if I try to do the index rebuild with online option, what will happen? Will it fail or fallback to offline rebuild?

  2. When an offline rebuild is running, and a CRUD query is fired, then does the query wait for rebuild to finish or does the rebuild fail and retry later?

Improve this question
0

1 Answer 1

Reset to default
2

It's pretty straight forward. You get blocking. When the index is being written, the query will wait until that completes. In fact, you're probably going to see a statement level recompile as well.

Improve this answer
3
  • Why won't it use table scan when working on creating index?
    – variable
    Oct 4, 2021 at 13:51
  • 1
    It can't when you are rebuilding a clustered index because table = index in that case.
    – MBuschi
    Oct 5, 2021 at 10:09
  • Pretty much, yes. While this behavior can be very inconvenient, we want it. We need it. It's a part of the ACID properties of the database and important for data integrity.
    – Grant Fritchey
    Oct 5, 2021 at 12:02

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