Understanding Online Create vs Rebuild Operations for Clustered and Unique Non-clustered indexes

Question

Hi

I have an 2014 Enterprise SQL Server that has a unique non-clustered index and a clustered index. The table has an "image" data type in it.

I had corruption in both indexes and I had to drop them and recreate them recently. The table is over 700 gigabytes in size.

I wanted to do the creation with "online" but I read this MS article:
https://learn.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations?view=sql-server-ver15

When you perform online index operations, the following guidelines apply:
Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains the following large object (LOB) data types: image, ntext, and text.

It took 10 hours to recreate both indexes and because of the above "guideline" I did both indexes offline so the database was not usable.

Going forward the "image" data type can be altered to a nvarchar(max) or varchar(max) or dropped with a code change. The column is empty at most sites but I still have some questions surrounding "online indexing/rebuilding".

1. I know the "old index" was still there even after I "dropped" it until the "new index" was built. I assume sql cannot access the "old index" because it is dropped but if I were to "rebuild the index online" and not "create" then sql could still use it. Is that correct?
2. I am confused by something else I read about "unique non-clustered indexes". Can you recreate those "online" and if yes, I assume there is only one exception if you "rebuild online" with unique constraints? I read when rebuilding a unique non-clustered index online where a concurrent user could violate the constraint during the rebuild. This would cause the online rebuild to fail in that case but it said it should rollback and I assume would cause an error to occur in your application. Is that correct?

Thank you,
Sue

Answer 1

Hi @OutThere2 ,

Welcome to Microsoft Q&A!

I know the "old index" was still there even after I "dropped" it until the "new index" was built.

Sorry, I am not very clarify for your question. Are you mean disable an index? When you disable an index, the metadata about the index is retained, as are the usage statistics in sys.dm_db_index_usage_stats. The index is not usable by the query optimizer, however. If you have an index hint in a query specifying the index, the query will fail. Also, foreign key constraints are disabled. When you drop an index, the metadata, statistics, and index pages are removed. If you drop a clustered index, the table will become a heap. Once an index has been dropped, it can’t be rebuilt – it must be created again. Please refer to this blog to get more information.

I am confused by something else I read about "unique non-clustered indexes".

Online index operations are not available in every SQL Server edition. As my research, only Enterprise version can do online index create and rebuild, please get the details from RDBMS high availability. The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations. If one user is rebuilding an nonclusterd index, that user and others can continue to update and query the underlying data. Please refer to Perform Index Operations Online to get more details.

If I have misunderstanding your question, please let me know.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

No matter you rebuild an index online or offline, the old index is there until the operation completes (so that it easily can roll back in case of an error). With an offline rebuild, SQL Server does not have to worry about updates to the table, since the table is locked. With an ONLINE rebuild, the table is available and rows may be inserted, updated or deleted while the rebuild is running. SQL Server using the version store in tempdb to track these changes and incorporate them to the new version of the index.

The concern you raise in your second question is unfounded. At least, this is nothing I have heard of.