We have a clustered index with 4 non clustered indexes. I want to rebuild them without taking services offline or dramatically hindering performance. Below is the script I tried to use with the resulting error:
CREATE CLUSTERED INDEX [ixc_MyTable] ON [dbo].[MyDatabase]
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [MyFilegroup]
Msg 2725, Level 16, State 2, Line 1
An online operation cannot be performed for index 'ixc_MyTable' because the index contains column 'ACL' 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.
I tried it with DropExisting=OFF and got this error:
Msg 1913, Level 16, State 1, Line 5
The operation failed because an index or statistics with name 'ixc_MyTable' already exists on table 'dbo.MyDatabase'.
After I rebiuld the clustered indexes I planned on rebuilding the non clustered indexes online using code like the following:
ALTER INDEX ix_OtherId ON dbo.MyTable
REBUILD WITH(FILLFACTOR=80,ONLINE = ON)