SQL Index
SQL CREATE INDEX Statement
Table indexes are created using the CREATE INDEX statement.
Data can be retrieved from the database more quickly when using indexes. The indexes are only there to speed up searches and queries; users cannot see them.
Because the indexes also need to be updated, updating a table with indexes takes longer than updating a table without them. Thus, only index columns that are likely to be often searched against.
CREATE INDEX Syntax
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column1, column2, ...);
CREATE UNIQUE INDEX Syntax
Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);
Note: various databases have various syntaxes for building indexes. Thus, make sure to review the syntax in your database before generating any indexes.
CREATE INDEX Example
An index called “idx_lastname” is created on the “LastName” column in the “Persons” database by the SQL statement that follows:
CREATE INDEX idx_lastname
ON Persons (LastName);
You can list the column names in the parenthesis, separated by commas, if you wish to establish an index on a subset of the columns:
CREATE INDEX idx_pname
ON Persons (LastName, FirstName);
DROP INDEX Statement
A table’s index can be removed using the DROP INDEX statement.
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;
