Primary vs secondary indexes in DBMS Theory - Quick Revision & Key Differences

Recall & Review

beginner

What is a primary index in a database?

A primary index is an index based on the primary key of a table. It uniquely identifies each record and is used to quickly locate data. It is usually automatically created when a primary key is defined.

beginner

What is a secondary index in a database?

A secondary index is an index created on non-primary key columns. It helps speed up queries on those columns but does not enforce uniqueness or directly identify records.

intermediate

How does a primary index differ from a secondary index?

A primary index is based on the primary key and uniquely identifies records, while a secondary index is based on other columns and may not be unique. Primary indexes are often clustered, meaning data is stored in the order of the index, but secondary indexes are usually non-clustered.

beginner

Why are secondary indexes useful?

Secondary indexes allow faster searching and retrieval of data based on columns other than the primary key. They improve query performance when filtering or sorting by those columns.

beginner

Can a table have multiple secondary indexes?

Yes, a table can have multiple secondary indexes on different columns to optimize various queries. However, each index adds some overhead for data updates.

Which index uniquely identifies each record in a table?

A Composite index

B Secondary index

C Primary index

D Foreign key index

The primary index is based on the primary key and uniquely identifies each record.

What is true about secondary indexes?

A They are based on non-primary key columns

B They are always unique

C They store data in sorted order

D They replace the primary index

Secondary indexes are created on columns other than the primary key and may not be unique.

Which of the following is a benefit of secondary indexes?

A Enforcing data uniqueness

B Speeding up queries on non-primary key columns

C Reducing storage space

D Automatically updating primary keys

Secondary indexes help speed up queries on columns other than the primary key.

Can a table have more than one secondary index?

A Yes, multiple secondary indexes can exist

B No, only one secondary index is allowed

C Only if the table has no primary key

D Only if the database is NoSQL

Tables can have multiple secondary indexes on different columns.

Which index type is usually clustered, storing data in index order?

A Hash index

B Secondary index

C Bitmap index

D Primary index

Primary indexes are often clustered, meaning the data is stored in the order of the index.

Explain the main differences between primary and secondary indexes in a database.

Think about uniqueness and which columns the indexes are built on.

Describe why secondary indexes are important and when you would use them.

Consider queries that filter by columns other than the primary key.