Question

When is it preferable to use a dense index rather than a sparse index? Explain.

Answer 1

​​​​​​​The following lines explain the use case when it is more preferred to use dense index instead of sparse index.

Suppose you have a file which is not sorted on the indexed field (this is very common when the index is a secondary index).

Example: data in table is sorted on ID:

  ID    Last Name
   1    Smith
   2    Francis
   3    Jones
   4    Zygoski
   5    Bohr
   6    Josephson
   7    Michaels
   8    Able

And, imagine this is a sparse index on Last Name (with pointers to the appropriate records:

Index
  Able
  Francis
  Jones
  Smith

Now, to find "Bohr", You go to "Able" and then search sequentially. Unfortunately, "Able" is the last record in the file, so, you can't get to "Bohr" from there.