5

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);
  • I've made massive updates to my answer (not sure if you get pinged when I do). – Aaron Bertrand Aug 20 '14 at 21:32
5

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

  • Wouldn't that quote conflict with the suscessful creation of my [NIDX_BIGMAX_FILLER2] index in the example above? – JBone Aug 19 '14 at 20:21
  • 2
    @JBone Why would it? Where in your creation did you explicitly tell that index to be created online? Only your rebuilds were specified online. I don't see any evidence that the create happened online, do you? – Aaron Bertrand Aug 19 '14 at 20:23
  • Ahh, good point. I have edited the creates to be online and it still works though. What gives!! :) – JBone Aug 20 '14 at 14:57
  • 2
    @JBone I've repro'd that scenario, and yes, the documentation (and error message IntelliSense provides you in a tooltip, even though the actual execution succeeds) seems to conflict with the actual behavior. I have sent in an e-mail to some Microsoft contacts and will update when I hear more. I suspect this operation was enabled in 2012 with the rest of the online operation improvements, but IntelliSense and Books Online have not been updated to reflect it. – Aaron Bertrand Aug 20 '14 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.