ALTER INDEX statement

ALTER { INDEX index-name 
| [ INDEX ] FOREIGN KEY role-name 
| [ INDEX ] PRIMARY KEY } 
ON [ owner.]object-name { REBUILD | rename-clause | cluster-clause } }

object-name : table-name | materialized-view-name

rename-clause : RENAME { AS | TO } new-index-name

cluster-clause : CLUSTERED | NONCLUSTERED

rename-clause Specify the new name for the index, primary key, or foreign key.
When you rename the underlying index for a foreign or primary key, the corresponding RI constraint name for the index is not changed. However, the foreign key role name, if applicable, is the same as the index name and is changed. Use the ALTER TABLE statement to rename the RI constraint name, if necessary. See ALTER TABLE statement.
cluster-clause Specify whether the index should be changed to CLUSTERED or NONCLUSTERED. Only one index on a table can be clustered.
REBUILD clause Use this clause to rebuild an index, instead of dropping and recreating it.

The ALTER INDEX statement carries out two tasks:

It can be used to rename an index, primary key, or foreign key.
It can be used to change an index type from nonclustered to clustered, or vice versa.

The ALTER INDEX statement can be used to change the clustering specification of the index, but does not reorganize the data. As well, only one index per table or materialized view can be clustered.

ALTER INDEX cannot be used to change an index on a local temporary table. An attempt to do so results in an Index not found error.

This statement cannot be executed when there are cursors opened with the WITH HOLD clause that use either statement or transaction snapshots. See Snapshot isolation.

Must own the table, or have REFERENCES permissions on the table or materialized view, or have DBA authority.

Automatic commit. Clears the Results tab in the Results pane in Interactive SQL. Closes all cursors for the current connection. If ALTER INDEX REBUILD is specified, a checkpoint is performed.

CREATE INDEX statement

SQL/2008 Vendor extension.

The following statement renames the index IX_product_name on the Products table to ixProductName:

ALTER INDEX IX_product_name ON Products
RENAME TO ixProductName;

The following statement changes IX_product_name to be a clustered index:

ALTER INDEX IX_product_name ON Products
CLUSTERED;

ALTER INDEX statement

Syntax

Parameters

Remarks

Permissions

Side effects

See also

Standards and compatibility

Example