- 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
If it was created using the
OFFLINE option then the index would be exclusively locked from access until it's completely finished being created.
- 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: