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

The ALTER INDEX statement carries out two tasks:

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.

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
• ALTER TABLE statement
• Snapshot isolation

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;