Sql-server – Cannot rebuild index, but there’s no reason why not

clustered-indexsql-server-2008t-sql

I've created a process whereby I am able to only rebuild indexes that need rebuilding(the process takes an hour and a half if I rebuild them all), and while it works beautifully, it gets stuck on one particular index and I see no reason why I should.

It fails with the following message:

Msg 2725, Level 16, State 2, Line 1

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

However, when I run the query based on a suggestion by this chap, shown below, I get no results:

SELECT *
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
AND ((c.system_type_id IN (34,35,99,241)) -- image, text, ntext, xml
 OR (c.system_type_id IN (167,231,165) -- varchar, nvarchar, varbinary
     AND max_length = -1))
INNER JOIN sys.indexes as si
on si.object_id = ic.object_id
AND ic.index_id = si.index_id
inner join sys.tables t
on t.object_id = ic.object_id
where t.name = 'CONTACTPERSON'
and si.name = 'I_520CUSTVENDRELIDX'

On top of that, a manual inspection of the index in question shows no text, ntext, image, xml or varchar(MAX), nvarchar(MAX) or varbinary(MAX). Could there be something I'm missing here?

For the record, this is a clustered index.

Best Answer

I skipped the code example, and jumped to what seems to be the real question here

On top of that, a manual inspection of the index in question shows no text, ntext, image, xml or varchar(MAX), nvarchar(MAX) or varbinary(MAX). Could there be something I'm missing here?

For the record, this is a clustered index.

You certainly are missing something, and the answer is in the body of the error message:

For a clustered index, the column could be any column of the table

For Online Index Operations there is a difference between a Clustered and a Non-Clustered index.
both may not contain a BLOB column in order to rebuild online,
but while a Non-Clustered Index "contains" the columns in it's definition (and included columns),
a Clustered Index "contains" the entire table with all of the columns.

in your case there is a column named MEMO which is of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type.
this column is part of the table, and thus part of the Clustered index.

Your options are:

  1. Perform the REBUILD operation offline
  2. Upgrade to SQL Server 2012
  3. Change the column's data type

Guidelines for Performing Online Index Operations

Related Question