ALTER INDEX statement

Renames an index, primary key, or foreign key, or changes the clustered nature of an index.

Syntax

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

Parameters

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.
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.

Remarks

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.

Privileges

To alter an index on a table, you must be the owner of the table, or have one of the following privileges:

REFERENCES privilege on the table
ALTER ANY INDEX system privilege
ALTER ANY OBJECT system privilege

To alter an index on a materialized view, you must be the owner of the materialized view, or have one of the following privileges:

ALTER ANY INDEX system privilege
ALTER ANY OBJECT system privilege

Side effects

Automatic commit. Closes all cursors for the current connection. If ALTER INDEX REBUILD is specified, a checkpoint is performed.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The following statement changes IX_product_name to be a clustered index:

ALTER INDEX IX_product_name ON GROUPO.Products
CLUSTERED;

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

ALTER INDEX IX_product_name ON GROUPO.Products
RENAME TO ixProductName;