Removing an Index

Remove an index from the data dictionary by using the DROP INDEX command. 

Syntax

DROP INDEX index;

To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege. 

Altering an Index

To modify an existing table’s index by rebuilding, or reorganizing the index.

ALTER INDEX IndexName

ON TableName REBUILD;

Confirming Indexes

You can check the different indexes present in a particular table given by the user or the server itself and their uniqueness. 

Syntax:

SELECT * from USER_INDEXES;

It will show you all the indexes present in the server, in which you can locate your own tables too.

Renaming an Index

You can use the system-stored procedure sp_rename to rename any index in the database.

Syntax:

EXEC sp_rename

index_name,

new_index_name,

N’INDEX’;

SQL Server Database

Syntax:

DROP INDEX TableName.IndexName;  

An index is a schema object. It is used by the server to speed up the retrieval of rows by using a pointer. It can reduce disk I/O(input/output) by using a rapid path access method to locate data quickly.

An index helps to speed up select queries and where clauses, but it slows down data input, with the update and the insert statements. Indexes can be created or dropped with no effect on the data. In this article, we will see how to create, delete, and use the INDEX in the database.