• If the added field in the select list is not in the index, the query optimizer only has two options:

    1. Look up the rows you need with the nonclustered index, then use the rowIDs to go back to the clustered index/data page and look up the additional field needed (called a key lookup)

    2. Run through the clustered index/heap, which has all the data, and find all the rows needed.

    The query optimizer will choose what it believes is the cheapest of those two options depending on stats.

    Bottom line is that if you want to solely use a nonclustered index, you need to make sure any fields that are being used for filtering are part of the index, and any fields that are being returned are at least included.


    [font="Tahoma"]Personal blog relating fishing to database administration:[/font]

    [font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]