8

When attempting to drop a non-clustered index using the online option such as DROP INDEX [IX_MYINDEX] ON [dbo].[myTable] WITH ( ONLINE = ON ), I receive the following error message.

Msg 3745, Level 16, State 1, Line 16 
Only a clustered index can be dropped online.

The SQL Server documentation clearly states:

The ONLINE option can only be specified when you drop clustered indexes.

https://msdn.microsoft.com/en-us/library/ms176118.aspx

But can someone please explain to me why this is the case? In my experience, you would be much more likely to drop a non-clustered index than a clustered index, since the clustered index in most cases is also your primary key.

0
12

Because dropping of a NCI is already as much online as it gets. Is a metadata only operation. There is not even data deletion, a dropped index rowset is simply deallocated, ie. the same operation as truncate does.

Dropping a clustered index, on the other hand, implies a rebuild and is a size-of-data operation, so it does make sense to have an online alternative.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.