Indexes: When Column Selectivity Is Not Always A Requirement

  • Misha_SQL

    SSCertifiable

    Points: 5402

    Great article, thank you!

    I think this sentence has a typo: "Now we have a Seek Predicate on DateAdded and a Predicate filter on DelFlag with a much-improved query cost (and less logical reads)."  -- looks like "DelFalg" should be "Gender" instead

  • Mike Byrd

    Ten Centuries

    Points: 1326

    Great catch!  And that was with 4 other sets of eyes reviewing the article before I submitted it.

     

    Mike Byrd

  • Mike Byrd

    Ten Centuries

    Points: 1326

    Comments posted to this topic are about the item Indexes: When Column Selectivity Is Not Always A Requirement

    Mike Byrd

  • Michael Gowan

    SSC Enthusiast

    Points: 161

    I think this shows why indexes should lead with the equality columns, then inequality columns.  I used to be in that camp; order the index columns by selectivity.  Now I avoid date fields for leading columns of indexes even if they're very selective.

  • Mike Byrd

    Ten Centuries

    Points: 1326

    I would rephrase your first sentence to ..."why indexes should lead with equality columns and then range searches."  Inequality columns (from your query) will almost always cause a scan rather than a seek.

    Mike Byrd

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply