Use the script below to rebuild an index on a table

ALTER INDEX   MY_INDEX_NAME on MY_TABLE_NAME  REBUILD  ; 

 Check SQL Server BOL for a the full list of options

 Notes

1)  Issuing an Index Rebuild request , drops and recreates the index

2) If  rebuilding a  CLUSTERED INDEX ,  nonclustered indexes won’t be rebuilt unless ALL keyword is used.

3) If no options are added , the statement inherits the options in sys.indexes

Why rebuild an index?

1)       Before rebuilding an index – ask the question “is the gain worth the cost of rebuilding the index?”  Rebuilding an index  can be a costly exercise , particularly on very large indexes.

2)       Strategy for large imports of data .  A typical strategy is disable NONCLUSTERED INDEXES , import the data and  rebuild the NONCLUSTERED INDEXES

3)       Index troubleshooting

4)     Index disable is issued  and the index needs to be enabled

Related Posts:

Disable Index and Rebuild Index for Updates on very large tables

SQL SERVER DISABLE INDEX

Author: Jack Vamvas (http://www.sqlserver-dba.com)