What are the different types of Indexes in SQL.

1. Primary Index

In primary index, there is a one-to-one relationship between the entries in the index table and the records in the main table. Primary index can be of two types:

Dense Primary Index

The number of entries in the index table is the same as the number of entries in the main table. In other words, each and every record in the main table has an entry in the index.

Sparse or Non-Dense PrimaryIndex

For large tables the Dense Primary Index itself begins to grow in size. To keep the size of the index smaller, instead of pointing to each and every record in the main table, the index points to the records in the main table in a gap.

2. Clustering Index

It may happen sometimes that we are asked to create an index on a non-unique key, such as Dept-id. There could be several employees in each department. Here we use a clustering index, where all employees belonging to the same Dept-id are considered to be within a single cluster, and the index pointers point to the cluster as a whole.

3. Secondary Index

While creating the index, generally the index table is kept in the primary memory (RAM) and the main table, because of its size is kept in the secondary memory (Hard Disk). Theoretically, a table may contain millions of records (like the telephone directory of a large city), for which even a sparse index becomes so large in size that we cannot keep it in the primary memory. And if we cannot keep the index in the primary memory, then we lose the advantage of the speed of access. For very large table, it is better to organize the index in multiple levels.