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:
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".
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?
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?