Clarification on Clustered index scan and Clustered Index Seek

Question

I have created one table with 80000 records with Cluster Index on CustomerID column. When I queried the following statements the execution plan behaves in differently. Can anyone clarify the same.

select * from customer

Showing Cluster Index Scan, So this is correct.

select * from customer where CustomerID=80000

Execution plan shows ClusterIndex Seek, So this is correct.

select * from customer where CustomerID between 1 and 70000

Execution plan still shows ClusterIndex Seek why?

As per the statistics the row count is 90% then the optimizer have to use 'ClusterIndex Scan' but it's using 'Cluster Index Seek' Why? All statistics are up to date and I have checked the Estimated row and actual rows are same then why the optimizer choose 'Cluster Index Seek' For getting 90% of rows among total records.

Answer 1

Execution plan still shows ClusterIndex Seek why?

The initial seek down the b-tree is to find the first row where CustomerID >= 1.

From that point on, the storage engine remembers the current scan position, and returns the next row in index order that qualifies each time a row is requested by a parent plan operator. The scan comes to an end as soon as a row is encountered that does not match the predicate CustomerID <= 70000.

The effect is that the 'seek' is an initial seek, followed by a partial ordered scan of the index.

This is usually more efficient than scanning the whole index, even where 90% of the rows are expected to qualify. The key point to get straight in your mind is that a separate b-tree seek is not performed for each row.

Answer 2

The answer would be simple here, assuming you just have one clustered index on table and your query is like

select * from customer where CustomerID between 1 and 70000

In above case seek would be preferred by optimizer because first the index would search the data based on condition CustomerID=1 and would find the first row which matches the predicate. Then it would eventually go down the index till it reaches end of the range that is 7000 to get the relevant data. So all together query engine has to read only relevant pages to give you information and so that would still qualify under seek.