Online Index Operations involving certain large data types are allowed in SQL Server 2012
Posted by decipherinfosys on April 3, 2012
Prior to SQL Server 2012, when you did online index operations (available in Enterprise Edition), if the index contained certain large data types (specifically varchar(max), nvarchar(max), varbinary(max)), then those online operations were not allowed on that index. That restriction does not exist anymore in SQL Server 2012 so there is no need to take such indexes out of your online operations.
Example: Let’s create a sample table which contains two columns – an ID column of type INT and the second column of data type NVARCHAR(MAX) and then let’s try to create an index on it with ONLINE option set to ON.
CREATE TABLE tblTestOnlineIdx
ID INT IDENTITY NOT NULL
, COL1 NVARCHAR(MAX) NOT NULL
, COL2 XML NOT NULL
CREATE INDEX tblTestOnlineIdx_IND_1 on tblTestOnlineIdx (ID) INCLUDE (COL1) WITH (ONLINE = ON)
We will see that we will get an error in SQL Server 2008 telling us that:
Msg 2725, Level 16, State 2, Line 1
An online operation cannot be performed for index ‘tblTestOnlineIdx_IND_1’ because the index contains column ‘COL1’ of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.
And this same code will run fine on SQL Server 2012 because this limitation has been removed. However, even though the BOL link above states that the same is true for indexes containing a XML data type column, that is not the case. ONLINE=ON is not a valid option for the XML indexes and it will still give an error. That is reflected in the BOL link here which covers the creation of XML indexes.