SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Disabling Indexes

By Andy Warren,

One of the new features that came with SQL 2005 was the ability to disable an index. On the surface that sounds interesting, but in practice I'm not sure it's a something many of us will use often. Today we'll explore the topic and see what you think.

We'll start by reviewing what it does:

  • Disabling a non clustered index leaves the current index on disk but marks it so that it is no longer maintained (no savings in disk space, savings from not maintaining it as rows change)
  • Disabling a clustered index makes the table unavailable (a neat trick, but useful?)
  • You cannot disable a primary key (which is enforced using an index) if the table is published as part of a transactional replication publication
  • If you disable an index on an indexed view the index data is deleted (different than for standard clustered/non clustered as above)
  • To enable an index we have to rebuild it (or check the 'use index' box on the index options table, which also triggers a rebuild)

So why use it?

  • A service pack may disable an index and warn you so that you can rebuild when it's complete it it cannot guarantee the data is valid (niche scenario I think)
  • Work around a disk IO error on an index page
  • Removing the index because you think you no longer need it
  • Rebuilding an index when space is short because this way we don't keep the original copy on disk, freeing up that space

Of those the only one I think we might find interesting on a day to day basis is to disable an index for troubleshooting. If you were using SQL 2000 and thought that there was index that was no longer needed, the typical DBA response was to script out the index and then drop it. Scripting it out first was our safety net in case we were wrong! In SQL 2005 we can substitute the use of disable to achieve the same thing, as long as we remember to return to delete the index once we know all is well. Otherwise the next DBA will be wondering if the index is supposed to be disabled or should it be dropped.

The other reasons aren't compelling; we can achieve the same effect by dropping the index and recreating it (the exception being the behavior of the service pack). My first thought when I heard about the behavior was that it would be interesting for bulk load scenarios where we would typically remove all the indexes. Rather than having to generate the drop and create statements, we could just disable all the indexes, load, and then rebuild all at the end. But disabling the clustered index doesn't really work because it makes the table unavailable. If I have to script out one index I can just as easily script them all out.

There are a few different ways to set an index to disabled. Believe it or not, the first way is when you create the index in Management Studio. This is the option tab:

Once an index has been created the easiest way to disable it is by just right clicking on the index, then selecting disable:

Or we can use TSQL:

alter index ndxEmailAddress on Person.Contact disable

Attemping to disable the clustered index does bring up a nice warning dialog:

If we proceed and then follow up with a select against the table, we get this nice error message:

Strangely there is no corresponding 'enable' function on the right click menu, nor do the indexes show as disabled in the Object Explorer view (at least on my machine!), though we can easily see which indexes have been disabled by running this query:

select * from sys.indexes where is_disabled = 1

As mentioned earlier to bring an index back online (enabled) it is necessary to rebuild it. That may sound bad, but it's no more work than would have been required if we dropped the index and then decided to add it back later.

So that's a quick run through of a feature that doesn't turn out to be all that useful to me. What about you, do you prefer disabling over dropping just as a way to do a task, or have you found a more interesting point of view that leads you to prefer disabling? Comments welcome in the attached discussion forum!

I blog frequently at http://blogs.sqlservercentral.com/andy_warren/default.aspx, come visit.


Total article views: 10613 | Views in the last 30 days: 2
Related Articles

SQL Server Disable Indexes and Rebuild Indexes dynamically

To generate the ALTER INDEX..DISABLE  and  the ALTER INDEX..REBUILD statements for all nonclustered ...


disable/enable indexes for table

This procedure disables or rebuilds indexes for a given tablename


Cannot find index error

Disable and rebuild index



Rebuilding indexes for better performance.


Rebuild Index

Rebuild Index

sql server 2005