The problem is I don't understand--no I DO understand selectivity--but I keep reading different definitions of it, which is confusing.
What I think (based on this by Gail Shaw): Selectivity is what predicates have. Columns aren't selective. Indexes aren't selective. Operators aren't selective. Selectivity is a measure of the percentage of rows that the predicate affects.
The confusion:
SQL Server Execution plans 3rd edition, Grant Fritchey. Page 223. He says
The selectivity of a predicate, for a given index, is the expected ratio of matching rows. Count the total number of rows in the table (z), count the number of distinct values (x) for a given column, or combination of columns, across all the rows, and then (x/z) gives the selectivity of the index, for an equality predicate comparing the column (or columns) against unknown values.
A highly selective index will have a low selectivity value. For example, a selectivity of 0.01 (1%) means that the optimizer expects 1% of the total rows in the table to match the predicate. Conversely, the worst possible selectivity is 1.0 (or 100%) meaning that every row will match the predicate condition.
Eh? I thought highly selective, i.e. 100%, i.e. 100% of the values are distinct, was a GOOD thing. But he says 100% is the worst possible selectivity.
Then in this article, they calculate the selectivity of a column with 2 distinct values (gender) to be 0.02%. But 0.02% isn't good surely.