What is the primary index in DBMS?

In the primary index there is a one-to-one relationship between the entries in the index table and records in the main table. Primary index is used on a primary key.

Types of Primary Index

Primary index are of two types, which are as follows −

Dense Primary Index

In a dense primary index an index record appears for every search key value in the main table that means each and every record in the main table has an entry in the index.

Example

Given below is an example of dense primary index −

A pointer stores the address of a record.

Non-Dense / Sparse Primary index

An index record appears as limited or for some of the values in the file.

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.

Example

Given below is an example of non-dense primary index −

As we can see, the record is divided into several blocks, each containing a fixed number of records. In the above example each table contains 10 records. The pointer in the index table points to the first record of each block.

If you are searching for roll 14, the index is first searched to find out the highest entry which is smaller than or equal to 14. We have 11. The pointer leads us to roll 11 where a sequential search is made to find out roll 14.