Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Only a clustered index can be dropped online.

Posted on 2010-08-12
4
Medium Priority
?
421 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 20

Accepted Solution

by:
Marten Rune earned 668 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:Chris M
Chris M earned 668 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 664 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

618 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