SQL CREATE INDEX Statement

In SQL, an index is a special lookup table that the database search engine can use to speed up data retrieval. Simply put, it's like an index in the back of a book—it points directly to the data you need, making searches much faster.

Creating Indexes

CREATE INDEX Syntax (Allows Duplicates)

This creates a standard index. Duplicate values are allowed in the indexed column(s).

CREATE INDEX index_name
ON table_name (column1, column2, ...);
      

CREATE UNIQUE INDEX Syntax (No Duplicates)

This creates a unique index. Duplicate values are *not* allowed in the indexed column(s). This is often used for primary keys.

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);
      

Important Note on Index Syntax

The precise syntax for creating indexes can vary slightly depending on the specific database system you are using (e.g., MySQL, PostgreSQL, SQL Server, Oracle). Always refer to your database's documentation for the most accurate and up-to-date syntax.

Important Note on Index Updates

Creating indexes on a table will improve query speed but will slow down write operations (inserts, updates, and deletes) because the index needs to be updated along with the table. For this reason, it is recommended to only create indexes on columns that will be frequently searched or filtered.

CREATE INDEX Examples

Creating a Single-Column Index

This creates an index named 'idx_lastname' on the 'LastName' column of the 'Persons' table.

CREATE INDEX idx_lastname
ON Persons (LastName);
      

Creating a Multi-Column Index

This creates a composite index (an index on multiple columns) on the 'LastName' and 'FirstName' columns.

CREATE INDEX idx_pname
ON Persons (LastName, FirstName);
      

Dropping Indexes

To remove an index, use the DROP INDEX statement. The syntax varies slightly across different database systems.

MS Access

DROP INDEX index_name ON table_name;
      

SQL Server

DROP INDEX table_name.index_name;
      

DB2/Oracle

DROP INDEX index_name;
      

MySQL

ALTER TABLE table_name DROP INDEX index_name;