opc.three (8/14/2012)

---

SQLSACT (8/10/2012)

---

HowardW (8/10/2012)

---

It's not relevant how many rows were actually returned, it still has to access a range in the index if it's not marked as UNIQUE as it doesn't know how many rows may be returned until it's performed the seek.

Please note that this is not the same as a table/index scan as you'd see it in an execution plan. It's still only scanning the rows that match the criteria...

Thanks

So anytime SQL has to access more that one page to return the desired result (irrespective of the number of rows returned). It counts as a range_scan ?

It has nothing to do with how many pages are read, it has to do with how many leaf-level index entries may need to be read. If you ask the engine to return the answer to this query:

select Col1 from Indexing2 where Col4 = '6365478'

and there is an non-unique index on Col4 then SQL Server has to prepare to scan the index to find all records you requested. In your case you know you are only retrieving one row however the index is not unique so SQL Server has to check at minimum 2 records (might be on the same index page, might not be) to return you one row, i.e. it seeks to the initial row but then continues by scanning the index until Col4 != '6365478'. The fact that you only had one row in your index where Col4 = '6365478' is irrelevant, SQL Server still had to do a range scan to satisfy your query.

but then continues by scanning the index until Col4 != '6365478'