XML FeedsThere 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 '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
- 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
- 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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.