Clustered index failed to build with Online=ON

Question

Clustered index failed to build with Online=ON

haiao2000

Hall of Fame

Points: 3734
More actions
November 21, 2014 at 12:12 pm

#312789

Here is my question. I have Enterprise version of SQL Server 2012 & SQL server 2008. I understand that Image/Text/NText is obsoleted and should not be used. That being said I dont understand why I couldnt rebuild the following clustered index, while I could with nonclustered index, this happens on both SQL 2008 and 2012. Here are the DDL. - Thanks in advance

CREATE TABLE [dbo].[Demo](
[ID] [int] NOT NULL,
[FK_ID] [uniqueidentifier] NOT NULL,
[SomeColumn] [nvarchar](100) NOT NULL,
[Image] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX IX1_Demo ON Demo (ID,FK_ID,SomeColumn) ON [PRIMARY]
CREATE UNIQUE NONCLUSTERED INDEX IX2_Demo ON Demo (ID,FK_ID,SomeColumn) ON [PRIMARY]
--online rebuild index failed on clustered index with this error.
ALTER INDEX IX1_Demo ON Demo REBUILD WITH(ONLINE = ON)
/*
Msg 2725, Level 16, State 2, Line 14
An online operation cannot be performed for index 'IX1_Demo' because the index contains column 'Image' of data type text, ntext, image or FILESTREAM. 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.
*/
--Online rebuild works fine on non clustered index
ALTER INDEX IX2_Demo ON Demo REBUILD WITH(ONLINE = ON)
--It seems to me that some how having the Image datatype column in the table is an issue. eventhough that column is not part of the index. How does that makes any sense.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

haiao2000 Hall of Fame Points: 3734 More actions · Answer 1

I guess I answer my own question. Found out that the table contains a column with image datatype is not allowed online rebuild.

This column needs to be on nvarchar(max) but not on FileStream in order to use online mode.

Thanks!

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

Glad to hear you worked it out, and thanks for posting the answer too. Now when someone who has the same problem does a search, they'll know what the solution is.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

greatrajani SSC Journeyman Points: 97 More actions · Answer 3

Due to huge data online clustered index rebuilt is not easy. so better to take database offline

Regards

Rajani Karthik

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

haiao2000 (11/21/2014)
Here is my question. I have Enterprise version of SQL Server 2012 & SQL server 2008. I understand that Image/Text/NText is obsoleted and should not be used. That being said I dont understand why I couldnt rebuild the following clustered index, while I could with nonclustered index, this happens on both SQL 2008 and 2012. Here are the DDL. - Thanks in advance

CREATE TABLE [dbo].[Demo](
[ID] [int] NOT NULL,
[FK_ID] [uniqueidentifier] NOT NULL,
[SomeColumn] [nvarchar](100) NOT NULL,
[Image] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX IX1_Demo ON Demo (ID,FK_ID,SomeColumn) ON [PRIMARY]
CREATE UNIQUE NONCLUSTERED INDEX IX2_Demo ON Demo (ID,FK_ID,SomeColumn) ON [PRIMARY]
--online rebuild index failed on clustered index with this error.
ALTER INDEX IX1_Demo ON Demo REBUILD WITH(ONLINE = ON)
/*
Msg 2725, Level 16, State 2, Line 14
An online operation cannot be performed for index 'IX1_Demo' because the index contains column 'Image' of data type text, ntext, image or FILESTREAM. 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.
*/
--Online rebuild works fine on non clustered index
ALTER INDEX IX2_Demo ON Demo REBUILD WITH(ONLINE = ON)
--It seems to me that some how having the Image datatype column in the table is an issue. eventhough that column is not part of the index. How does that makes any sense.

If the ID column is "ever increasing", there's not much reason to rebuild the clustered index as part of any maintenance plan.

I'd also think that the clustered index would be much better if it were on just the ID column instead of the 3 columns that you have.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)