Indexes: When Column Selectivity Is Not Always A Requirement

Question

Indexes: When Column Selectivity Is Not Always A Requirement

Mike Byrd

Ten Centuries

Points: 1387
More actions
September 16, 2020 at 8:44 pm

#3787096

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

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

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

Misha_SQL SSCertifiable Points: 5406 More actions · Answer 1

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: 1387 More actions · Answer 2

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

Mike Byrd

Michael Gowan SSC Enthusiast Points: 161 More actions · Answer 3

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: 1387 More actions · Answer 4

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