What is the difference between a primary index and a secondary index?
The correct answer and explanation is :
Difference Between a Primary Index and a Secondary Index
A primary index is a unique, ordered file associated with the primary key of a database table, ensuring fast and efficient access to records. A secondary index, on the other hand, is an additional index that allows queries on non-primary key attributes, improving search efficiency without enforcing uniqueness.
Explanation (300 Words)
In database management systems (DBMS), indexes are used to speed up data retrieval. Two common types of indexes are primary index and secondary index, which serve different purposes.
A primary index is built on the primary key, which is a unique attribute (or a combination of attributes) that identifies each record in a table. Since a primary key is unique, a primary index is always dense (i.e., every record has an index entry) or sparse (indexing only certain records while maintaining order). It directly influences how data is stored on disk, often leading to clustered storage where rows are physically ordered based on the primary key.
A secondary index, however, is built on attributes that are not primary keys. Unlike a primary index, a secondary index does not control data storage order and allows duplicate values. Secondary indexes help improve query performance by allowing searches on non-primary key attributes, such as customer names or email addresses, which are frequently queried. Secondary indexes are typically dense, meaning every record has an entry.
For example, in a table of employees, an index on Employee_ID (primary key) is a primary index, while an index on Department or Salary is a secondary index.
In summary:
- Primary Index → Built on primary key, controls data order, unique, can be sparse.
- Secondary Index → Built on non-primary key attributes, does not control data order, allows duplicates, always dense.
Both indexes enhance query performance, but the primary index is fundamental for database organization, while the secondary index improves search flexibility.