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.

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

"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 - used with older versions of SQL Server
sys.indexes
sys.index_columns
sys.dm_db_index_physical_stats
sys.xml_indexes
EVENTDATA

Equivalent Oracle commands:

ALTER INDEX