Indexes are an important part of the database architecture that helps retrieving the data faster. Generally indexes are created on columns that are used in condition statements or join conditions.
Implicit Index: When a table is defined with a Primary key or Unique Key constraints the DBMS system creates indexes by default. This is called as Implicit Index.
Explicit Index: When an index created using the “Create” command that is called Explicit Index.
Based on the way the indexes are stores in database they are categorized as Clustered or Non-clustered Indexes.
When you create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. It is more efficient when the query uses the primary key for retrieval.
Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it’s row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.
If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.
Commonly used syntax for creating Index:
CREATE INDEX “INDEX_NAME” ON “TABLE_NAME” (COLUMN_NAME)