Solved

Only a clustered index can be dropped online.

Posted on 2010-08-12
4
367 Views
Last Modified: 2012-05-10
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
Comment
Question by:anushahanna
4 Comments
 
LVL 20

Accepted Solution

by:
Marten Rune earned 167 total points
ID: 33428024
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
 
LVL 12

Assisted Solution

by:pastorchris
pastorchris earned 167 total points
ID: 33428727
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 166 total points
ID: 33451333
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
 
LVL 6

Author Comment

by:anushahanna
ID: 33570862
Thanks very much experts, for the light on the subject.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now