Archive

Archive for the ‘Documentation’ Category

Online Index Operation – Doc Bug –

July 23, 2012 2 comments

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.

%d bloggers like this: