Sql-server – Which index types do not support online index rebuilds

Ok, so the documentation is not exactly stellar, but you must think this shouldn't work online because the doc says:

Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns.

Which - if you invert it - states that an index that includes a MAX type, for example, can't be created online.

Now, the emphasis on the word created was put there by me - this stated restriction does not apply to rebuild (which I guess explains why rebuild isn't mentioned explicitly in that sentence). In your updated code sample, you actually have demonstrated that both rebuild and create, with the option to run online, run successfully and don't return an error. I have confirmed that this is the case (and at least in the create scenario, that conflicts with the sentence above). I have also received confirmation from Microsoft that the doc is wrong.

In fact, in the table below that (for both the 2012 doc and the 2014 one), there is a list of the types of indexes that are not supported by rebuild or create, and LOB types are no longer a constraint (whether part of the base table or included in the index being created or rebuilt). These are the only indexes that you can't rebuild online:

• Disabled clustered index or disabled indexed view
• XML index
• Index on a local temp table

And these are the only indexes that you can't create online:

• XML index
• Initial unique clustered index on a view
• Index on a local temp table

So this is kind of like reverse documentation - you didn't experience a problem, and this document didn't exactly lead you to believe that you should experience a problem. Well, not intentionally. I don't know that there is a document in MSDN that says, "Listen up yo, you can now create and rebuild indexes online when LOB columns are part of the equation!" - but as I pointed out above, others have blogged about it.

In the end, you are correct, and the documentation is wrong , as is the IntelliSense tooltip you get when you try to do the same - it will underline the index name as if there is an error, even though when executing the code, it works successfully. Click to enlarge:

Whether the creation or rebuild actually happens online, I did not test; only confirming that it did not generate an error, as IntelliSense (and the document above) would have you believe. But according to Microsoft and Remus' blog above, it should work as expected (forget what the doc currently says).

I've filed the following Connect item to deal with the IntelliSense problem:

• SSMS : IntelliSense incorrectly warns index with MAX can't be created online

Back in June of 2012, I filed this item against the documentation, which at the time still had an entry in the table explaining that LOB types invalidated online operations.

• [DOC] : ALTER INDEX topic still says no online rebuild with LOB

That Connect item was marked as fixed, because the table was corrected, but the sentence I highlight above still needs to be fixed. I'm waiting to hear if I should petition to have my existing item re-opened (we used to be able to do that ourselves), or create a new one. I'll update here when I hear back. The Connect item has been re-opened and the topic owner has received a detailed e-mail. :-)

Under the covers sp_indexoption just does an ALTER INDEX as well.

My recommendation would be to forget about sp_indexoption and just utilize ALTER INDEX:

alter index IX_YourIndex
on dbo.YourTable
set
(
    allow_row_locks = on
);
go

As noted in the BOL reference for ALTER INDEX, when you specify just the set options:

Specifies index options without rebuilding or reorganizing the index