ALTER INDEX

Modify an existing index on a table or view.

Syntax
      ALTER INDEX { index_name | ALL } ON object ABORT [ ; ]

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

      ALTER INDEX { index_name | ALL } ON object RESUME [ WITH (resumable_index_option [ , ...n ] ) ] [ ; ]

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

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

      ALTER INDEX { index_name | ALL } ON object REORGANIZE  [ PARTITION = partition_number ]
       [ WITH ( reorganize_option  ) ] [ ; ]

      ALTER INDEX { index_name | ALL } ON object PAUSE [ ; ]


object ::=
{
    { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
}

rebuild_index_option ::=
{
      PAD_INDEX = { ON | OFF }
    | FILLFACTOR = fillfactor
    | SORT_IN_TEMPDB = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | STATISTICS_INCREMENTAL = { ON | OFF }
    | ONLINE = { ON [ ( low_priority_lock_wait ) ] | OFF }
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = time [ MINUTES ]
    | ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { partition_number [ TO partition_number ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { partition_number [ TO partition_number ] } [ , ...n ] ) ] }

The default FILLFACTOR is 0  A larger value, up to 99 will preallocate space for the index during creation.
An explicit FILLFACTOR setting applies only when the index is first created or rebuilt.


It is not recommended to disable row or page locks on an index. Concurrency-related problems
might occur, and certain functionality might be unavailable. For example, an index can't be
reorganized when ALLOW_PAGE_LOCKS is set to OFF.

single_partition_rebuild_index_option ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = time [ MINUTES ]
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
    | ONLINE = { ON [ ( low_priority_lock_wait ) ] | OFF }
}

reorganize_option ::=
{
       LOB_COMPACTION = { ON | OFF }
    |  COMPRESS_ALL_ROW_GROUPS =  { ON | OFF }
}

set_index_option ::=
{
      ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
}

resumable_index_option ::=
 {
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION = time [ MINUTES ]
    | low_priority_lock_wait
 }

low_priority_lock_wait ::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = time [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}


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

-- Disable an index:
ALTER INDEX PK_DemoID ON demo.T_demo DISABLE;

-- Enable an index
ALTER INDEX PK_DemoID ON demo.T_demo REBUILD;

-- 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).
ALTER INDEX at Microsoft.com
sys.indexes
sys.index_columns
sys.dm_db_index_physical_stats
sys.xml_indexes
EVENTDATA