Index creation in SQL: Which command is used to create a new index on a table or cluster to improve query performance on key columns?

Introduction / Context:
Indexes accelerate lookups, joins, and ordering by providing alternative access paths to data. Database designers must know the precise SQL used to create and manage indexes to balance performance and storage overhead.

Given Data / Assumptions:

• We need the canonical SQL command that introduces a new index.
• We assume mainstream relational systems using standard terminology.
• We are not altering or setting attributes of an existing index; we are creating one.

Concept / Approach:
The standard DDL command is CREATE INDEX, typically of the form “CREATE INDEX idx_name ON table_name (col_list)”. Alternative phrasings such as “MODIFY INDEX” or “SET INDEX TO FILE” are not standard SQL. Altering index properties (where supported) still begins with CREATE for initial creation.

Step-by-Step Solution:
Identify the action: creation of an index, not modification.Recall standard DDL syntax: CREATE INDEX idx ON table(columns).Select the command that matches: CREATE INDEX.

Verification / Alternative check:
Vendor manuals consistently define CREATE INDEX with column lists and options (unique, include columns, storage parameters, partitions). The foundational verb is always CREATE for new indexes.

Why Other Options Are Wrong:

• MODIFY INDEX: Non-standard; modification is limited and vendor-specific.
• SET INDEX TO FILE: Not an SQL DDL command.
• All of the above: Overinclusive and incorrect.
• None of the above: Incorrect because CREATE INDEX is valid.

Common Pitfalls:
Over-indexing can slow writes; always match indexes to workload patterns and maintain statistics for the optimizer.

Final Answer:
CREATE INDEX