Index selectivity

When SQL Server evaluates if an index should be used to retrieve data in a query, selectivity is one of the most important factors. Of course, given that the index column matches the filter or join columns.

If a column only has one or very few unique values, index selectivity is low. True/false columns and gender is common examples.

If there are only unique values in a column, index selectivity is high. Primary keys meets these requirements as they are always unique.

SQL Server favors indexes that are built on columns with high selectivity, since this may return fewer rows and therefore at a lower cost.

If the index has multiple columns, it is preferable (if possible) that the columns are in the order of their selectivity (most selective first).

So how is selectivity computed?


Continue ReadingIndex selectivity