7

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.

  • I did read the remarks section. It did not seem to clarify anything for me, because unless I missed something, it only mentions clustered indexes in the remarks section, and makes no mention of why the online option cannot be used for non-clustered indexes. – BateTech Jan 23 '15 at 19:42
  • @BateTeck yes, sorry for that, disregard my previous comment. – ypercubeᵀᴹ Jan 23 '15 at 19:49
  • 4
    My wild guess is that it's usually fast to drop a non-clustered index. No serious disadvantage from the lock and no need to delay the dropping for some queries that my need to use the index. A clustered index drop on the other hand has some serious work to do, i.e. convert the table from a clustered (index) to a heap and also update all the other non-clustered indexes. So it can be slow and ONLINE = ON has obvious benefits. – ypercubeᵀᴹ Jan 23 '15 at 19:52
  • That makes sense that it would only need a Sch-M lock, and then in effect it can treat the index as disabled so that no new queries use it, and then it won't matter how long the deletion of the index data takes, since no new queries could use it anyway. However, I have not been able to confirm this and not sure how to setup a test to see what locks are actually taken behind the scenes during the drop index operation. – BateTech Jan 23 '15 at 19:59
  • @ypercube: you should had put that as an answer, I've only read your comment after posting mine – Remus Rusanu Jan 23 '15 at 20:06
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.

  • Thank you for the info. The comparison to truncate is very helpful. Your explanation makes perfect sense, but how would you determine that this is a "metadata only" operation if you didn't already know? I could not find it in any official MS documentation. – BateTech Jan 23 '15 at 22:24
  • I did find this sentence in drop index documentation: "When a nonclustered index is dropped, the index definition is removed from metadata and the index data pages (the B-tree) are removed from the database files". So does that mean 1) the index object is removed from metadata, so no future queries will use (or even be aware of) the index, 2) then the index data pages are removed from the database files, which won't require blocking locks b/c no other queries should be accessing the index data since it was already removed from meta-data and technically no longer exists. Is this correct? – BateTech Jan 23 '15 at 22:28
  • 2
    The easiest way is to analyze the log. – Remus Rusanu Jan 23 '15 at 22:36
  • 2
    As for second question: you are describing deferred drop. – Remus Rusanu Jan 23 '15 at 22:39

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.