Sep 17, 2013

An online operation cannot be performed for index '' because the index contains column '' 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.


Suppose we have created a table tblData:

CREATE TABLE tblData(
    ID INT ,
    Data VARCHAR(MAX)
)

Now if will create  a index on table tblData using following sql statement:

CREATE NONCLUSTERED  INDEX  IX_Data
ON tblData(ID) INCLUDE(Data)
WITH(ONLINE = ON)

We will get error message like:

An online operation cannot be performed for index '' because the index contains column '' 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.

Cause: If we are creating index which includes columns of data types text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type ONLINE option must be OFF.

Solution:

CREATE NONCLUSTERED  INDEX  IX_Data
ON tblData(ID) INCLUDE(Data)
WITH(ONLINE = OFF)

No comments:

Post a Comment