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

T-SQL
1
Create table TestIndex (id int, somecol varchar(20))
Create table TestIndex (id int, somecol varchar(20))

Insert a little bit of data

T-SQL
1
2
3
4
  insert into TestIndex
  select number,CONVERT(varchar(20),getdate(),100)
  from master..spt_values
  where type = 'p'
  insert into TestIndex
  select number,CONVERT(varchar(20),getdate(),100)
  from master..spt_values
  where type = 'p'

Create a nonclustered index

T-SQL
1
  create index ix_TestIndex on TestIndex(id,somecol)
  create index ix_TestIndex on TestIndex(id,somecol)

Now let’s disable this index

T-SQL
1
2
3
  ALTER INDEX ix_TestIndex
  ON TestIndex
  DISABLE
  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

T-SQL
1
2
3
4
5
6
  set showplan_text on
  go
  select * from TestIndex
  go
  set showplan_text off
  go
  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

T-SQL
1
2
3
  ALTER INDEX ix_TestIndex
  ON TestIndex
  REBUILD
  ALTER INDEX ix_TestIndex
  ON TestIndex
  REBUILD

Now we will run the same query again

T-SQL
1
2
3
4
5
6
   set showplan_text on
  go
  select * from TestIndex
  go
  set showplan_text off
  go
   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

T-SQL
1
    drop index TestIndex.ix_TestIndex
    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

T-SQL
1
create clustered index ix_TestIndexClustered on TestIndex(id,somecol)
create clustered index ix_TestIndexClustered on TestIndex(id,somecol)

Now let’s disable this clustered index

T-SQL
1
2
3
  ALTER INDEX ix_TestIndexClustered
  ON TestIndex
  DISABLE
  ALTER INDEX ix_TestIndexClustered
  ON TestIndex
  DISABLE

And now when we run the query from before

T-SQL
1
2
3
4
5
6
7
  set showplan_text on
  go
  select * from TestIndex
  go
  set showplan_text off
  go
  
  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 ‘ix_TestIndexClustered’ 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

T-SQL
1
2
insert into TestIndex
select 2,'Bla'
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 ‘ix_TestIndexClustered’ on table or view ‘TestIndex’ is disabled.

If we rebuild the clustered index again

T-SQL
1
2
3
  ALTER INDEX ix_TestIndexClustered
  ON TestIndex
  REBUILD
  ALTER INDEX ix_TestIndexClustered
  ON TestIndex
  REBUILD

And if we run this query again

T-SQL
1
2
3
4
5
6
  set showplan_text on
  go
  select * from TestIndex
  go
  set showplan_text off
  go
  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