Professional SQL Server™ 2005 Programming

8.5. Choosing Wisely: Deciding What Index Goes Where and When

By now, you're probably thinking to yourself, "Gee, I'm always going to create clustered indexes!" There are plenty of good reasons to think that way. Just keep in mind that there are also some reasons not to.

Choosing what indexes to include and what not to can be a tough process, and, in case that wasn't enough, you have to make some decisions about what type you want them to be. The latter decision is made simultaneously easier and harder in the fact that you can only have one clustered index. It means that you have to choose wisely to get the most out of it.

8.5.1. Selectivity

Indexes, particularly non-clustered indexes, are primarily beneficial in situations where there is a reasonably high level of selectivity within the index. By selectivity, I'm referring to the percentage of values in the column that are unique. The higher the percentage of unique values within a column, the higher the selectivity is said to be, and the greater the benefit of indexing.

If you think back to the sections on non-clustered indexes — particularly the section on non-clustered indexes over a clustered index — you will recall that the lookup in the non-clustered index is really only the beginning. You still need to make another loop through the clustered index in order to find the real data. Even with the non-clustered index on a heap, you still end up with multiple physically separate reads to perform.

If one lookup in your non-clustered ...