Since it does cost based optimization , based on 'estimates' SQL decides to go with the least costly plan. Depending upon , say , how stale are the stats for example , the optimizer will prefer doing a scan if its cheaper (at plan level) than using the index .