Contents Index ALTER FUNCTION statement ALTER PROCEDURE statement

ASA SQL Reference
  SQL Statements

ALTER INDEX statement


Description 

Use this statement to rename an index or foreign key, or to change the clustered nature of the index.

Syntax 

ALTER { index-spec { rename-clause | cluster-clause }
foreign-key-spec { rename-clause | cluster-clause }
primary-key-spec cluster-clause }

index-spec :
INDEX index-name ON [ owner.]table-name

foreign-key-spec :
INDEX ] FOREIGN KEY role-name ON [ owner.]table-name

primary-key-spec :
INDEX ] PRIMARY KEY ON [ owner.]table-name

rename-clause :
RENAME [ AS | TO ] index-name

cluster-clause :
CLUSTERED | NONCLUSTERED

Parameters 

rename-clause    Specify the new name for the index.

cluster-clause    Specify whether the index should be changed to CLUSTERED or NONCLUSTERED. Only one index on a table can be clustered.

Usage 

The ALTER INDEX statement carries out two tasks:

Permissions 

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

Side effects 

Automatic commit. Clears the Results tab in the Results pane in Interactive SQL. Closes all cursors for the current connection.

See also 

CREATE INDEX statement

Standards and compatibility 
Example 

The following statement renames the index ix_prod_name on the product table to ixProductName:

ALTER INDEX ix_prod_name ON product
RENAME TO ixProductName

The following statement changes ix_prod_name to be a clustered index:

ALTER INDEX ix_prod_name ON product
CLUSTERED

Contents Index ALTER FUNCTION statement ALTER PROCEDURE statement