high selectivity concept

ali_alannah used Ask the Experts™
these days am studying Index in  sql server , i read some confusing terminology !! like (high/low selectivity)
can any body help me to understand wat that means? but please explin by EXAMPLes!!!!
and simplify you explanation
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
the selectivity is what goes with the cardinality concept.
the "cardinality" refers to the number of "distinct" values, as in the set theory

so, take a column "SEX". the possible values are "male" and "female" (ignoring for the moment other possible values like "unknown" or even "other" ) ...
so, your cardinality for that column would be 2, no matter how many rows you have in that table.

the selectivity is the "number of rows" / "cardinality", so if you have 10K customers, and search for all "female", you have to consider that the search would return 10K/2 = 5K rows, so a very "bad" selectivity.

the column for the primary key on the other side is "unique", and hence the cardinality is equal to the number of rows, by definition.
so, the selectivitiy for searching a value in that column will be 1, by definition, which is the best selectivity possible

hope that clarifies the concept of cardinality and selectivity


You Are Perfect

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial