ALTER INDEX

Modify an existing index on a table or view.

Syntax
      ALTER INDEX {index | ALL} ON object DISABLE [;]

      ALTER INDEX {index | ALL} ON object REORGANIZE
         [PARTITION = partition_number ]
            [WITH ( LOB_COMPACTION = {ON | OFF} ) ] [;]

      ALTER INDEX {index | ALL} ON object REBUILD
         [ [WITH ( rebuild_index_option [ ,...n ] ) ]
            | [PARTITION = partition_number
               [ WITH ( single_ptn_rebuild_index_option
                  [ ,...n ] )
               ] 
            ]
         ] [;]

      ALTER INDEX {index | ALL} ON object SET ( set_index_option [ ,...n ] )

Object:
     database.[schema].table_or_view
     schema.table_or_view

rebuild_index_option:
   PAD_INDEX  = {ON | OFF}
   FILLFACTOR = fillfactor 
   SORT_IN_TEMPDB = {ON | OFF}
   IGNORE_DUP_KEY = {ON | OFF}
   STATISTICS_NORECOMPUTE = {ON | OFF}
   ONLINE = {ON | OFF} 
   ALLOW_ROW_LOCKS = {ON | OFF}
   ALLOW_PAGE_LOCKS = {ON | OFF}
   MAXDOP = max_degree_of_parallelism

single_partition_rebuild_index_option:
   SORT_IN_TEMPDB = {ON | OFF }
   MAXDOP = max_degree_of_parallelism

set_index_option:
   ALLOW_ROW_LOCKS = {ON | OFF}
   ALLOW_PAGE_LOCKS = {ON | OFF}
   IGNORE_DUP_KEY = {ON | OFF}
   STATISTICS_NORECOMPUTE = {ON | OFF}

Key:
   ALL          All indexes associated with the table or view
   ASC/DESC     The sort direction for the index column. 
   PARTITION    Only rebuild/reorganize one partition of the index. 
   partition_number  The number of an existing index partition to rebuild/reorganize.
   LOB_COMPACTION  Compacting large object (LOB) data can reduce the disk space used.
   fillfactor   Percentage of each index page to fill during index creation/rebuild.
                1-100, default=0  (values 0 and 100 are identical=Full)
   SET          Change index options without a rebuild/reorg, the index must be enabled.
   ONLINE       In enterprise edition, index operations may be performed online.

In a default (nonclustered) index, the physical order of the data is independent of the index order.

Reindexing should be scheduled during system down-time as it is likely to very severely affect performance.

The options ONLINE and IGNORE_DUP_KEY are not valid when you rebuild an XML index.

A unique clustered index must be created on a view before any nonclustered index is created.

Examples

ALTER INDEX MyIndex01 ON MySchema.MyTable
REBUILD;
GO

ALTER INDEX MyIndex02 ON MySchema.MyTable
REBUILD Partition = 5;
GO

ALTER INDEX ALL ON MySchema.MyTable
REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
GO

--
Script to rebuild all the indexes for a database:
USE SS64_db --Change this to the name of your database

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
 SELECT table_name FROM information_schema.tables
 WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
 ALTER INDEX ON schema.table REBUILD/REORGANIZE
 FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

"Anything you build on a large scale or with intense passion invites chaos” ~ Francis Ford Coppola

Related commands

CREATE INDEX
CREATE PARTITION SCHEME
Data Types
DBCC SHOW_STATISTICS
DROP INDEX
DBCC DBREINDEX - use to re-index with older versions (SQL Server 2000 and earlier)
sys.indexes
sys.index_columns
sys.dm_db_index_physical_stats
sys.xml_indexes
EVENTDATA
Equivalent Oracle command: ALTER INDEX