• This is mainly relevant to non-clustered indexes, of course. ]If you specify the first column on a clus index, naturally it does not need to be selective to use the clus index 🙂 .]

    What you have to keep in mind is that SQL is very selective when deciding whether to use a non-clus index. Often the data needs to be as selective as 3%(!) for SQL to use a nc index, esp. when a clus index exists.

    So knowing the selectivity will help you decide whether it's likely to be worthwhile to build an index at all.

    Of course the easiest way is still to just review the actual query plans :-). But some front-end planning and understanding of your data selectively could still be useful.

    Scott Pletcher, SQL Server MVP 2008-2010