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.