[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 423
  • Last Modified:

Only a clustered index can be dropped online.

why is the above restriction?

you can try this code to reproduce it:

create table oneT (one1 int, two2 int)
insert oneT select 1,2
create index onei  on oneT(one1)
DROP INDEX [onei] ON dbo.oneT WITH (ONLINE = ON)
0
anushahanna
Asked:
anushahanna
3 Solutions
 
Marten RuneCommented:
ONLINE = ON | OFF
Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.
Link: http://msdn.microsoft.com/en-us/library/ms176118.aspx

Since a nonclustered index does not hold any underlying tables and associated index the ONLINE is not an option when it comes to nonclustered indexes. The errormessage could in my opinion be clearer, alternatively the index could be dropped since online/offline is the same when it comes to nonclustered indexes.

Regards Marten
0
 
Chris MConsulting - Technology ServicesCommented:
I'll explain briefly what happens when a non-clustered index is dropped (deleted) and compare it to dropping a clustered index.

On dropping a non-clustered index, the index definition is removed from metadata and the index data pages (call it the B-tree) are also removed from the database files.

On dropping a clustered index, the index definition is removed from metadata and the data rows that were stored in the leaf of the clustered index are then stored in the resulting unordered table (referred to as a heap). All the space previously occupied by the index is regained. This space can then be used for any database object after.

It's within the above two comparisons that it's easier to refer to index data pages of a clustered index while it's being dropped if the option "ONLINE = ON " is specified.

If ONLINE = ON option is specified, SQL server will not hold long-term locks on the table.which allows queries and/or updates to the underlying table to continue.

The trick is with utilising the chance on how the data rows are treated as I explained earlier above, making it possible for a clustered index to have that option available.


Regards,
Chris Musasizi

.  Senior DBA on Oracle, Microsoft SQL Server, Postgres, MySQL,  and IBM  Informix
. Developer on multiple platforms & languages
.  Enterprise Admin (Linux/Unix & Windows Server Systems)
. Storage  Admin
. Systems Engineer & Solutions Developer (Windows Server  Systems & Windows Solutions Developer)
. Database Architect

.  Certified Expert on Experts Exchange
. Article Author and script  writer on SQL Server Central (SQLserverCentral.com)
. Contributor on  stackoverflow.com and Microsoft TechNet.
0
 
Mark WillsTopic AdvisorCommented:
It is simple, it really is referring to how to handle any locks - so sometimes easier to consider it a bit of a misnomer as to what is directing the database engine to really do...  and only really applies to Enterprise edition (or so the story goes :) )...

You are aware of clustered indexes actually holding data on the leaf nodes - think that has been covered off before, so, we probably dont have to worry too much about the structure - except to say - it is precisely that structure that makes it different for a clustered index.

It is only available / applicable to clustered indexes. Non clustered indexes are fairly simple definitions over the top of any clustered indexes and or data, so, really just dropping the definition. While it does that, it must hold a lock so that any queries or really the optimiser doesnt try to use an index that wont exist (for a variety of activities including the more obvious selects, updates etc but also updating statistics).

Well, that lock comment is not quite 100% correct, because really we are talking about three different types of locks. First with the online=on we are really issuing an "Intent Share" (IS) lock but when building or removing the metadata (ie the definition) we are holding a Schema Lock. Now, for index operations, we also need to momentarily hold a shared lock on the data while the index operation is happening - obviously a lot more activity is adding a new clustered index than dropping one.

There is a pretty good story about it in BOL : http://msdn.microsoft.com/en-us/library/ms191261.aspx

Suffice to say, the naming of that option is not exactly a clear definition of what is really going on, other than a momentary indication if a lock is going to render the data available (online) or locked (offline).

For me, I try to forget that it is an option :) It only becomes very important with very very large objects and highly accessed at that.
0
 
anushahannaAuthor Commented:
Thanks very much experts, for the light on the subject.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now