patternsqlModerate
Why can a non-clustered index not be dropped using online=on option
Viewed 0 times
clusteredwhycannondroppedonlineoptionusingindexnot
Problem
When attempting to drop a non-clustered index using the online option such as
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.
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#90042, answer score: 12
Revisions (0)
No revisions yet.