Online Index Operation – Doc Bug –

In SQL Server 2012, Index Online operation is supported for VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), and XML.  However the ALTER INDEX states it is not supported.  In my testing in other conflicting documentation, Guidelines for Online Index Operation, states it is supported.

Just to verify I did some testing with SQL Server 2012 RTM and found it works fine with ONLINE=ON.

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)

CREATE CLUSTERED INDEX idx1 On IndexTest(Col1)
CREATE NONCLUSTERED INDEX idx2 On IndexTest (Col2) INCLUDE (Col6, Col7, Col8)

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

ALTER INDEX idx1 ON IndexTest REBUILD WITH (ONLINE=ON)
ALTER INDEX idx2 ON IndexTest REBUILD WITH (ONLINE=ON)

I submitted a DOC BUG, it should be fixed; but please verify my results and vote Smile.

2 comments

  1. Thanks Aaron.

    Agreed on Duplicate. I searched Microsoft Connect to find similar topic but failed so I created mine. However the ALTER INDEX article still needs fixing, so I’ll leave mine open. Maybe they can merge it with yours, I’ll put reference to yours.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.