HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Clarification on Clustered index scan and Clustered Index Seek

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
clusteredseekclarificationscanandindex

Problem

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.

Solution

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.

Context

StackExchange Database Administrators Q#139905, answer score: 10

Revisions (0)

No revisions yet.