Practice DBMS Indexing MCQ questions with answers and explanations. These questions cover important concepts like dense index, sparse index, primary index, secondary index, and clustered index. Useful for IBPS IT Officer, GATE, and other competitive exams.
These DBMS Indexing MCQ are frequently asked in IBPS and GATE exams.
Difficulty: Easy to Medium
Total Questions: 15
Important DBMS Indexing MCQ Questions
Q1. A sparse index in a database is defined as an index where:
A. Every search key value has an index entry
B. Only some search key values (usually one per data block) have an index entry
C. The index is stored in a hash table format
D. The index is created on a non-ordering field
View Answer
Answer: B
Explanation: A sparse index contains entries for only some records, typically pointing to the start of a data block rather than every individual record.
Q2. Which type of index determines the physical order of data records in the disk file?
A. Secondary Index
B. Non-clustered Index
C. Clustered Index
D. Hash Index
View Answer
Answer: C
Explanation: A clustered index physically sorts the data in the table based on the indexed column, so only one clustered index can exist per table.
Q3. A dense index is characterized by having:
A. An entry for every search key value in the data file
B. An entry for every data block in the file
C. No pointers to the actual data records
D. Only unique values from the primary key
View Answer
Answer: A
Explanation: In a dense index, there is at least one entry for every search key value present in the data file.
Q4. Which of the following is true regarding Secondary Indexes?
A. They must be sparse
B. They dictate the physical storage order of the records
C. They are typically dense and do not affect physical storage
D. A table can have only one secondary index
View Answer
Answer: C
Explanation: Secondary indexes are created on non-ordering fields and do not affect physical storage, so they are typically dense.
Q5. A Primary Index is created on which of the following?
A. A non-key attribute of a sorted file
B. The primary key of a file where records are physically ordered by that key
C. Any random attribute in an unsorted file
D. A non-ordering, non-key attribute
View Answer
Answer: B
Explanation: A primary index is defined on the ordering key field of a physically ordered data file.
Q6. How many clustered indexes can a single database table have?
A. 0
B. 1
C. 2
D. Unlimited
View Answer
Answer: B
Explanation: Only one clustered index can exist because it defines the physical order of data.
Q7. Which index type requires extra storage space and works like a lookup table without changing record order?
A. Primary Index
B. Clustered Index
C. Secondary Index
D. Sparse Index
View Answer
Answer: C
Explanation: Secondary indexes store pointers to actual records and require additional space.
Q8. In indexing, “Uniqueness” is mandatory for:
A. Clustered Index
B. Primary Index
C. Secondary Index
D. Sparse Index
View Answer
Answer: B
Explanation: Primary index is based on the primary key, which must be unique.
Q9. Sparse indexes are preferred when:
A. The index must be as small as possible
B. Only point queries are used
C. Data is unsorted
D. It is a secondary index
View Answer
Answer: A
Explanation: Sparse indexes are smaller and more memory efficient.
Q10. Which index is used when many records share the same value but data is sorted?
A. Primary Index
B. Dense Index
C. Clustered Index
D. Secondary Index
View Answer
Answer: C
Explanation: Clustered indexes group similar values physically together.
Q11. In a Primary Index, the relation between index entries and records is:
A. 1:1
B. 1:Many
C. Many:1
D. Many:Many View Answer
View Answer
Answer: A (Dense case)
Explanation: In dense indexing, each entry maps to one record. In sparse indexing, one entry may point to multiple records (block-level).
Q12. Secondary indexes are mainly used for:
A. Sequential access
B. Faster search on non-primary key columns
C. Reducing file size
D. Sorting physical data
View Answer
Answer: B
Explanation: Secondary indexes improve search performance on non-key attributes.
Q13. A major disadvantage of dense index is:
A. Slower range queries
B. Higher storage requirement
C. Cannot be used for primary keys
D. No direct pointers View Answer
View Answer
Answer: B
Explanation: Dense index stores entries for every record, increasing storage usage.
Q14. Which index type points to blocks instead of individual records?
A. Dense Secondary Index
B. Sparse Primary Index
C. Multi-level Index
D. B+ Tree Leaf View Answer
View Answer
Answer: B
Explanation: Sparse indexes point to the beginning of blocks to reduce size.
Q15. If data is not sorted, the index must be:
A. Sparse
B. Dense
C. Clustered
D. Primary View Answer
View Answer
Answer: B
Explanation: Without sorting, every record must be indexed, so dense index is required.
Conclusion
These DBMS indexing MCQ help strengthen understanding of dense vs sparse index, primary vs secondary index, and clustered indexing concepts. Practice more sets to improve accuracy and speed for competitive exams.
Fore a detailed explanation of indexing concepts you can refer to DBMS Indexing Explained.