Index in SQL is created on existing tables and is a way to boost up the data retrieval performance of table.
Index should be created on the columns that are frequently used for searching data.
An Index is an ordered list of contents of a column ( or a group of columns) of a table.
Updating a table with index takes more time than updating a table without indexes as indexes also needs to be updated along with the data. So Indexes should only be created on the columns that are frequently used for searching.
SQL Create Index Syntax
CREATE INDEX index_name ON table_name (column_name1, column_name2,...);
Note that the Index created with above syntax can hold duplicate values for columns used for Indexing.
SQL Create Unique Index Syntax
Create a unique Index on the table
CREATE UNIQUE INDEX index_name ON table_name (column_name, column_name2,...);
Create Index Example
CREATE INDEX I_EMP_PHONE ON EMPLOYEE (FIRST_NAME, LAST_NAME);
Create Unique Index Example
CREATE UNIQUE INDEX I_EMP_PHONE ON EMPLOYEE (PHONE);
These Indexes are created by database itself when use constraints like
PRIMARY KEY or
They are created using the “create index.. ” syntax.
Drop Index Statement
DROP INDEX statement is used to delete an index in a table.
DROP INDEX index_name ON table_name
ALTER TABLE table_name DROP INDEX index_name