0

I understand that Nonclustered index uses bigger space than clustered index. Azure SQL provides option to create Nonclustered index using both ONLINE = { ON | OFF }.

  1. How is the NCI creation different for online and offline?
  2. Will there be any difference in disc size consumed while creating online and offline index? If yes, is there a way to calculate the disc size of online/offline index creation?
0

1 Answer 1

3
  1. How is the NCI creation different for online and offline?

Per Perform Index Operations Online:

The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations.

In other words, the index (in your case the nonclustered index) being created won't be exclusively locked from access during the entirety of the operation. Therefore other queries will be able to utilize it while it's undergoing such operation ONLINE.

If it was created using the OFFLINE option then the index would be exclusively locked from access until it's completely finished being created.

  1. Will there be any difference in disc size consumed while creating online and offline index? If yes, is there a way to calculate the disc size of online/offline index creation?

I believe the amount of disk space difference should be rather negligible usually but there can be a difference per Temporary Disk Space for Online Index Operations:

Online index operations use row versioning to isolate the index operation from the effects of modifications made by other transactions. This avoids the need for requesting share locks on rows that have been read. Concurrent user update and delete operations during online index operations require space for version records in tempdb.

So one of the biggest factors that would contribute to disk space overhead during an ONLINE operation would be if there are concurrent data changes to the fields that are part of the index. On a very heavily transactional database you may notice the difference in disk space overhead, but generally I believe it's not worth worrying about.

This makes the potential size difference variable based on the write-heaviness of your table during the index operation, and therefore not possible to proactively calculate before running the index operation.

Other related product documentation:

0

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.