Home Dashboard Directory Help
Search

ALTER INDEX in Books Online needs update for ONLINE operations by Mohit K. Gupta


Status: 

Closed
 as Fixed Help for as Fixed


2
0
Sign in
to vote
Type: Bug
ID: 754575
Opened: 7/19/2012 10:49:49 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

Alter Index Rebuild operation with online, allows online operations with VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), and XML. As per http://msdn.microsoft.com/en-us/library/ms190981.aspx.

However http://msdn.microsoft.com/en-us/library/ms188388.aspx, states it not allowed.

Details
Sign in to post a comment.
Posted by Microsoft on 10/2/2012 at 2:32 PM
The instructions in the Guidelines topic are more detailed. And I want to push readers to the more detailed info. So I have added the following to the ALTER INDEX topic beneath the table where it lists the requirements.
For more detailed information about index operations that can be performed online, see Guidelines for Online Index Operations.

Ideally this will be easier to maintain than having this info scattered amonst many topics.
Posted by Mohit K. Gupta on 10/2/2012 at 11:46 AM
Thanks Rick.

Agreed the updated documentation does not have that listed anymore. However they have removed too much information now. The ALTER INDEX WITH ONLINE=ON fails when you have image, ntext, and text column types still.

Example:
CREATE TABLE IndexTest (Col1 varchar(50) NOT NULL, Col2 Int NOT NULL, Col3 Char(10) NOT NULL, Col4 Int NOT NULL, Col5 Int NOT NULL, Col6 VARCHAR(MAX), Col7 VARBINARY(MAX), Col8 XML, Col9 text)

ALTER TABLE IndexTest Add Col0 INT CONSTRAINT pk PRIMARY KEY;
CREATE NONCLUSTERED INDEX idx2 ON IndexTest (Col2) INCLUDE (Col6, Col7, Col8)

INSERT INTO IndexTest (Col0, Col1, Col2, Col3, Col4, Col5, Col6, Col8) VALUES (1,'DUDE3RVGMDIDM6YVMVXSOLD3GP260FGBQDVMBEOSCWUL71Y8PE',72,'NK90OKFFXO',4,634, REPLICATE('N',10000), '<XML>JUNK</XML>')
INSERT INTO IndexTest (Col0, Col1, Col2, Col3, Col4, Col5, Col6, Col8) VALUES (2,'KUTSXDSNBK9HRT7XUGJ3V5NSVX366AP0YP69IX6H4CM0LNIC7P',20,'5L02R79DEN',9,778, REPLICATE('N',10000), '<XML>JUNK</XML>')

ALTER INDEX pk on IndexTest REBUILD WITH (ONLINE=ON)
ALTER INDEX ALL ON IndexTest REBUILD WITH (ONLINE=ON)

DROP TABLE Indextest

Both rebuild statements fail because of text data type.
Posted by Microsoft on 10/2/2012 at 10:43 AM
Recent updates to the topics you reference make it difficult to establish if your issue is still a problem. Please review the most recent versions of the topics and if you think it's a problem, let me know what section you think needs adjustment. Sorry I'm having trouble with this (though it's our own fault for messing this up to start with). Thanks. Rick Byham.
Posted by Mohit K. Gupta on 7/25/2012 at 7:39 AM
Sorry to update the issue, a bit, I was not reading carefully Do'h.

However the issuse statement is under "Specifying ALL with this operation". But again I just finished testing with no issues.

1) Create table with columns of field types; Create table t1 (Col1 int, Col2 int, Col3 varchar(max), Col4 xml, Col5 nvarchar(max), Col6 varbinary(max))
2) Create clustered index "Create clustered index idx1 on t1(Col1)"
3) Create non-clustered index "Create nonclustered index idx2 on t1(Col1) Include (Col3, Col4)"
4) Execute "Alter Index All On T1 Rebuild".

Operation completed successfully!
Posted by Mohit K. Gupta on 7/25/2012 at 7:28 AM
Agreed Aaron, however the Alter Index still list that Alter Index Rebuild Online is not allowed when LOB fields are present. And it lists all the fields...when it should only be image, ntext, and text.
Posted by AaronBertrand on 7/23/2012 at 7:15 AM
This is a duplicate and it's already being fixed.

http://connect.microsoft.com/SQLServer/feedback/details/748684/doc-alter-index-topic-still-says-no-online-rebuild-with-lob
Posted by Phil Brammer on 7/19/2012 at 7:30 PM
Just a correction to your feedback above "states it is now allowed" should read "states it is not allowed."
Sign in to post a workaround.