There are certain operations where dropping an index, loading data and then again creating the index can speed up data loading. SQL server 2005 introduced a way to disable an index.

Let’s take a look, first create this table

Create table TestIndex (id int, somecol varchar(20))

Insert a little bit of data

insert into TestIndex
  select number,CONVERT(varchar(20),getdate(),100)
  from master..spt_values
  where type = 'p'

Create a nonclustered index

create index ix_TestIndex on TestIndex(id,somecol)

Now let’s disable this index

ALTER INDEX ix_TestIndex
  ON TestIndex
  DISABLE

Now when we run our query against the table and look at the plan we get a table scan

set showplan_text on
  go
  select * from TestIndex
  go
  set showplan_text off
  go

|--Table Scan(OBJECT:([master].[dbo].[TestIndex]))
    
    

Now let’s rebuild the index again

ALTER INDEX ix_TestIndex
  ON TestIndex
  REBUILD

Now we will run the same query again

set showplan_text on
  go
  select * from TestIndex
  go
  set showplan_text off
  go

|--Index Scan(OBJECT:([master].[dbo].[TestIndex].[ix_TestIndex]))
    

As you can see, it uses the index again

Now let’s drop this index

drop index TestIndex.ix_TestIndex

Is there a difference how disable works between nonclustered and clustered indexes?

Let’s take a look, first create this clustered index

create clustered index ix_TestIndexClustered on TestIndex(id,somecol)

Now let’s disable this clustered index

ALTER INDEX ix_TestIndexClustered
  ON TestIndex
  DISABLE

And now when we run the query from before

set showplan_text on
  go
  select * from TestIndex
  go
  set showplan_text off
  go
  

We get this error

_Msg 8655, Level 16, State 1, Line 1

The query processor is unable to produce a plan because the index ‘ixTestIndexClustered’ on table or view ‘TestIndex’ is disabled.

As you can see while a clustered index is disabled the data is unavailable. Not only that, you can also not insert anything into the table,

So this query

insert into TestIndex
select 2,'Bla'

Fails with the same error from before

_Msg 8655, Level 16, State 1, Line 1

The query processor is unable to produce a plan because the index ‘ixTestIndexClustered’ on table or view ‘TestIndex’ is disabled.

If we rebuild the clustered index again

ALTER INDEX ix_TestIndexClustered
  ON TestIndex
  REBUILD

And if we run this query again

set showplan_text on
  go
  select * from TestIndex
  go
  set showplan_text off
  go

|--Clustered Index Scan(OBJECT:([master].[dbo].[TestIndex].[ix_TestIndexClustered]))

We can see that it does use the clustered index

My question to you.

So my question to you people is, do any of you use this instead of drop and create index? One advantage I see is that you don’t need to update the code that drops and recreates the non clustered index if your index definition changes when using disable index in your ETL process. If you disable a clustered index you can also not insert into the table.

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum