• Because the last nonclustered index that you added is covering (contains all the columns that the query needs) and is smaller than the cluster (because the cluster is the table). Hence scanning that NC index is faster and requires less IOs than scanning the cluster.

    In fact, both of the nonclustered indexes are covering, since a nonclustered index includes the clustering key. The two indexes are, for that query, equivalent and hence SQL can use either.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass