When it comes to indexing there is one term that seems to be rather unclear and in this post I will try to explain what is the most selective column you may get different definitions depending on who you ask and one that I come across is:
Most Selective Column:
- Is the column with the highest number of unique values
- a column that is most filtered on
To me (at least today) it is both so the most selective column is the one that is frequently used and is very unique.
To give you an example if we have invoice table. InvoiceID might be the most selective BUT it depends because if I'm interesting in analyzing a group of invoices then I might not use InvoiceID very frequently and may use date instead (paid date or date created etc). Which might potentially mean you may need both indexed and most selective depends on your query.