So, what we've got here is a table with a clustered index and two non-clustered indexes (one of which is the primary key). We run a query that accesses a range of values from one of the non-clustered indexes, but because it's not a covered query, each row must then fetch the additional columns by doing a bookmark lookup into the clustered index. These lookups are costly because each location read is essentially random and also because of the number of lookups. This is the reason you are advised to choose a clustered index for accessing ranges of rows. A covering index is a potentially suitable alternative for accessing ranges.
So, what we've got here is a table with a clustered index and two non-clustered indexes (one of which is the primary key). We run a query that accesses a range of values from one of the non-clustered indexes, but because it's not a covered query, each row must then fetch the additional columns by doing a bookmark lookup into the clustered index. These lookups are costly because each location read is essentially random and also because of the number of lookups. This is the reason you are advised to choose a clustered index for accessing ranges of rows. A covering index is a potentially suitable alternative for accessing ranges.
ReplyDelete