When to use a spare index over a dense index?
In context:


  when the file is not sorted on the indexed field (such as when the index is a secondary index)



"the file" = the database table (or similar object) being indexed
"the indexed field" = the field being considered for the new index. A sparse index relies on the records being sorted in the same order as the indexed field.


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.


  when the index file is small compared to the size of memory.


Basically, if the entire index can be loaded into memory, and still have plenty of room to load in the data from the table that's needed, then the database engine should be able to find each record in the index, and go directly to that record in the data table (instead of possibly having to pull in several pages from the table, scanning to locate the record it wants). If the dense index takes up too much room in memory, then either you have to read it in in parts, or it's going to squeeze the available memory for the rows you need from the data table.

Or, at least, that's my best guess. Note that I'm speaking form the view of someone who's been working with databases for almost 30 years; when I was in class, I don't remember dense vs sparse indexes being discussed.