ALTER INDEX (Transact-SQL)

Modifies the properties of an index.

Applies to: SQL Server 2008 and later

Syntax


ALTER INDEX { index_name | index_id }
ON { <table_or_view> }
{ . . . } [ ; ]

<table_or_view> ::=
    { base_table | indexed_view }

. . . ::=
    {
        { DISABLE | ENABLE } [ TOGGLE ] , . . .n
        | REBUILD [ WITH ( <rebuild_index_option> [ , . . .n ] ) ]
        | RESUME [ ( <resume_index_option> ) ]
        | ABORT [ ( <abort_index_option> ) ]
        | SET ( <set_index_option> [ , . . .n ] )
        | SET Statistics ( <set_statistics_option> [ , . . .n ] )
        | ON { partition_number | <<partition_number>> }
        |   { . . . }
    }

<rebuild_index_option> ::=
    {
        PAD_INDEX = { ON | OFF }
        | FILLFACTOR = <fillfactor>
        | SORT_IN_TEMPDB = { ON | OFF }
        | STDPENT = { ON | OFF }
        | IGNORE_DUP_KEY = { ON | OFF }
        | ONLINE = { ON | OFF }
        | ALLOW_ROW_LOCKS = { ON | OFF }
        | ALLOW_PAGE_LOCKS = { ON | OFF }
        | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
        | DATA_COMPRESSION = { OFF | ROW | PAGE }
        [ , <spatial_index_option> ]
        [ , <xml_index_option> ]
        [ , <column_store_index_option> ]
    }

<resume_index_option> ::=
    {
        ( ONLINE = ON )
    }

<abort_index_option> ::=
    {
        ( ONLINE = ON )
    }

<set_index_option> ::=
    {
        PACK_REMOVED_ROW_ACCESS = { ON | OFF }
    }

<set_statistics_option> ::=
    {
        FULL | SAMPLE
    }

<spatial_index_options> ::=
    {
        BOUNDING_BOX = ( <bounding_box> )
        | CELLS_PER_OBJECT = <cells_per_object>
        | DATAPLACE_LEVEL = { LOW | MEDIUM | HIGH }
        | ALTERNATIVE_BINNING = { ON | OFF }
        | DROP_EXISTING = { ON | OFF }
        | IGNORE_EXISTING = { ON | OFF }
        | MAX_GRID_LEVEL = <max_grid_level>
        | PAD_INDEX = { ON | OFF }
        | PER_rowIndex = { ON | OFF }
        | SORT_IN_TEMPDB = { ON | OFF }
    }

<column_store_index_option> ::=
    {
        DROP_EXISTING = { ON | OFF }
        | IGNORE_EXISTING = { ON | OFF }
    }

<xml_index_option> ::=
    {
        [ XML_TYPE_AUTO_GRID = { ON | OFF } ]
        [ XML_TYPE_FILTER_GRID = { ON | OFF } ]
        [ XML_SCHEMA_ COLLECTIONS = <xml_schema_collections> ]
        [ DROP_EXISTING = { ON | OFF } ]
        [ IGNORE_EXISTING = { ON | OFF } ]
    }

Description

The ALTER INDEX statement is used to modify the properties of an existing index or to reorganize or rebuild an index.

Key Operations Supported by ALTER INDEX:

Enabling/Disabling Indexes: You can disable an index to temporarily suspend its maintenance. This can be useful during large data loads. When re-enabled, the index is rebuilt.
Rebuilding Indexes: Rebuilding an index updates index statistics, de-allocates unused pages, and can improve performance. It can also be used to change index options like compression or fill factor.
Resuming/Aborting Online Index Operations: For online index operations (available in Enterprise editions), ALTER INDEX can be used to resume an interrupted operation or to abort a running one.
Setting Index Options: Various options like PACK_REMOVED_ROW_ACCESS can be modified.
Modifying Statistics: You can control the level of detail for index statistics.

Parameters

Parameter	Description
`index_name \| index_id`	The name or ID of the index to be modified.
`<table_or_view>`	The name of the table or view on which the index is defined.
`DISABLE \| ENABLE [ TOGGLE ]`	Disables or enables an index. `TOGGLE` switches the current state (enabled to disabled, or disabled to enabled).
`REBUILD [ WITH (...) ]`	Reorganizes and rebuilds the index. The `WITH` clause allows specifying various rebuild options.
`RESUME [ (...) ]`	Resumes an interrupted online index operation. Requires `(ONLINE = ON)`.
`ABORT [ (...) ]`	Aborts a running online index operation. Requires `(ONLINE = ON)`.
`SET (...)`	Modifies specific index properties like `PACK_REMOVED_ROW_ACCESS`.
`SET Statistics (...)`	Controls how statistics for the index are updated. `FULL` collects detailed statistics, `SAMPLE` collects sampled statistics.
`ON { partition_number \| <<partition_number>> }`	Specifies the partition number for index operations.
`<rebuild_index_option>`	Options that can be specified during index rebuild: `PAD_INDEX`: Specifies page-level padding. `FILLFACTOR`: Specifies the percentage of page fullness. `SORT_IN_TEMPDB`: Specifies whether temporary sort results are stored in `tempdb`. `STDPENT`: Specifies whether to drop existing indexes. `IGNORE_DUP_KEY`: Specifies behavior on duplicate key insertion. `ONLINE`: Allows index operations to run with minimal blocking. (Enterprise Edition feature). `ALLOW_ROW_LOCKS \| ALLOW_PAGE_LOCKS`: Controls lock granularity. `OPTIMIZE_FOR_SEQUENTIAL_KEY`: Optimizes for sequential key inserts. `DATA_COMPRESSION`: Specifies the compression type (`ROW` or `PAGE`).

Examples

Disable an index:


ALTER INDEX AK_ProductID ON Production.ProductDisable;

Rebuild an index with row compression:


ALTER INDEX PK_SalesOrderDetail ON Sales.SalesOrderDetail REBUILD WITH (DATA_COMPRESSION = ROW);

Rebuild a clustered index with a fill factor and online option:


ALTER INDEX PK_Person_PersonID ON Person.Person REBUILD WITH (FILLFACTOR = 90, ONLINE = ON);

Enable a disabled index:


ALTER INDEX AK_ProductID ON Production.Product ENABLE;

ALTER INDEX (Transact-SQL)

Syntax

Description

Key Operations Supported by ALTER INDEX:

Parameters

Examples

Disable an index:

Rebuild an index with row compression:

Rebuild a clustered index with a fill factor and online option:

Enable a disabled index:

See Also