ALTER INDEX – Correct approach to rebuild or reorganize an index

One of the vital functions for a database professional is to maintain index by reorganization or rebuilding. If you are using SQL Server from at least SQL 2000 era, then you must be familier to use dbcc reindex for similar purpose. In this post, I am going to discuss about “alter index” which is present from SQL Server 2005 onwards. As new release after SQL Server 2008 will remove dbcc reindex, it is a high time that all of us start using “alter index” and do all necessary code changes before next migration project.

Alter index can be used for a wide range of purpose from rebuilding or reorganization to using compression/enabling or disabling constraints etc. Please refer to http://msdn.microsoft.com/en-us/library/ms188388.aspx for a detailed document on all the features of “Alter Index” including advanced features like using partitioned index, using idex compression etc.

Reorgazation of index is always an online operation. Also rebuild index can be done online by specyfing REBUILD WITH (ONLINE = ON)clause on. Any operation (reorgazanation, rebuild, create, drop etc) related to index is recommended to be done online in a critical environment.

Here I am going to provide couple of most frequent use of “Alter Index”:

Rebuilding an index without specifying any option:

USE [DatabaseName];

GO

ALTER INDEX [IndexName] ON [TableName]

REBUILD ;

GO

Rebuilding an index with specifying option:

USE [DatabaseName];

GO

ALTER INDEX [IndexName] ON [TableName]

REBUILD WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

GO

Rebuilding all index of a table with specifying option:

USE [DatabaseName];

GO

ALTER INDEX ALL ON [TableName]

REBUILD WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON,              STATISTICS_NORECOMPUTE = ON);

GO

Reorganizing in index of a table:

USE [DatabaseName];

GO

ALTER INDEX [IndexName] ON [TableName]

REORGANIZE

;

GO

Reorganizing all index of a table:

USE [DatabaseName];

GO

ALTER INDEX ALL ON [TableName]

REORGANIZE

;

GO

Disabling an index:

Note: Disabling a clustered index prevents user access to the underlying table data. Also if we disable an index, only index definition is saved in system catlog and underlaying data is deleted. So you need to rebuild the index to enable it back.

USE [DatabaseName];

GO

ALTER INDEX [IndexName] ON [TableName]

DISABLE

;

GO

Enabling an index:

You have to rebuild the index to enable it.