In SQL database administration, which statement is correctly used to remove (drop) an existing index from the database schema?

Introduction / Context:
Managing physical structures such as indexes is a core task in relational database administration. Sometimes you need to eliminate an index to reclaim space, simplify maintenance, or because the optimizer no longer benefits from it. This question checks the precise SQL verb used to remove an index object.

Given Data / Assumptions:

• You are working with a standard SQL relational database.
• An index already exists and must be removed cleanly.
• No renaming or disabling is requested; the object should be dropped.

Concept / Approach:
In Data Definition Language (DDL), the verb ‘‘DROP’’ permanently removes a schema object. Therefore, to remove an index, the canonical statement is DROP INDEX index_name. Vendors may add qualifiers (such as ON table_name, or IF EXISTS), but the controlling keyword is always DROP.

Step-by-Step Solution:

Verification / Alternative check:
After running DROP INDEX, the data dictionary no longer lists the index, and the optimizer cannot reference it. Queries that relied on it will still run but may use different access paths until a replacement index is created.

Why Other Options Are Wrong:

• DELETE INDEX: DELETE is a Data Manipulation Language verb for rows, not schema objects.
• REMOVE INDEX: not valid SQL syntax.
• ROLL BACK INDEX: ROLLBACK undoes a transaction; it does not target an index object.
• None of the above: incorrect because DROP INDEX is correct.

Common Pitfalls:
Confusing disabling an index with dropping it, or using row-level verbs (DELETE) on schema-level objects.

Final Answer:
DROP INDEX