Monday, 29 August 2011

Online index rebuilds : Unsupported data types

I was attempting to rebuild some indexes ONLINE (using SQL 2005 Enterprise) when I got this error.

Msg 2725, Level 16, State 2, Line 4 Online index operation cannot be performed for index 'PKCI_MyTable_SearchIndex' because the index contains column 'ReportData' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.

The error tells us what the problem is, i.e. the inability to use certain data types when rebuilding indexes ONLINE. In my case, the offending column was a deprecated TEXT data type. Still, it reminded me to audit the database for such columns...

Finding deprecated data types-

SELECT
   sys_schemas.Name as [schema_name]
 , sys_tables.Name AS [table_name]
 , sys_columns.Name AS [column_name]
 , sys_types.Name AS [datatype_name]
FROM Sys.Tables sys_tables (nolock)
JOIN Sys.Schemas sys_schemas (nolock)
ON sys_schemas.Schema_Id = sys_tables.Schema_Id
JOIN Sys.Columns sys_columns (nolock)
ON sys_columns.Object_Id = sys_tables.Object_Id
JOIN Sys.Types sys_types (nolock)
ON sys_types.System_Type_Id = sys_columns.System_Type_Id
AND sys_types.Name IN ('text,'ntext','image')

See also Deprecated in SQL 2008 R2

No comments: