Sql-server – Online index rebuild LOB types

indexsql server

I have read and heard that you cannot perform online clustered index rebuild for tables that have a varchar(max) type. This (as I have read) also should be true for nonclustered indexes that contain varchar(max) types. http://msdn.microsoft.com/en-us/library/ms190981.aspx. However when proving this out (in both versions of SQL2012 and SQL2014) I do not run into errors when rebuilding these indexes online. Is this an error in the documentation?

As a side note the online rebuilds fail on 2008R2.

Test script is included below … 

CREATE TABLE BIGMAX ( X INT IDENTITY(1,1), Y VARCHAR(MAX), FILLER BIT DEFAULT 1, FILLER2 BIT DEFAULT 1);

INSERT INTO BIGMAX ( Y ) SELECT top 1000 replicate('1',10000) from sys.all_columns c , sys.all_columns c2

CREATE CLUSTERED INDEX [CIDX_BIGMAX] ON BIGMAX(X) WITH (ONLINE = ON);

CREATE NONCLUSTERED INDEX [NIDX_BIGMAX_FILLER] ON BIGMAX(FILLER) WITH (ONLINE = ON);

CREATE NONCLUSTERED INDEX [NIDX_BIGMAX_FILLER2] ON BIGMAX(FILLER) INCLUDE (Y) WITH (ONLINE = ON);

ALTER INDEX [CIDX_BIGMAX] ON [dbo].[BIGMAX]
REBUILD WITH (ONLINE = ON);

ALTER INDEX [NIDX_BIGMAX_FILLER] ON [dbo].[BIGMAX]
REBUILD WITH (ONLINE = ON);

ALTER INDEX [NIDX_BIGMAX_FILLER2] ON [dbo].[BIGMAX]
REBUILD WITH (ONLINE = ON);

Best Answer

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:

enter image description here 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:

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.

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. :-)

Related Question