CODEWITHSUNDEEP
sqlsql-server
Anoop
Anoop

Reputation: 2101

SQL Server Indexes

what is the difference between clustered index scan and clustered index seek?

Upvotes: 4

Views: 136

Answers (4)

Guffa
Guffa

Reputation: 700342

A seek is used when the condition fits the indexed data so that it can efficiently make use of the index, for example in a query like:

select Name from Table where Group = 42

As the comparison is a direct value, it can easily be used to locate the part of the index where the items are.

A scan is used when the condition is more complex so that every value in the index has to be evaluated, for example in a query like:

select Name from Table where right(cast(group as varchar), 2) = '00'

As the condition uses a calculated value from the index that can't easily be used to isolate a part of the index, all items has to be evaluated.

Upvotes: 1

Brannon
Brannon

Reputation: 26109

A clustered index scan is a table scan on a table that has a clustered index. By default a primary key is a clustered index, so basically a table that has a primary key.

A clustered index scan occurs when the predicate contains columns other than the primary key (and there is no other index available to satisfy the predicate).

A clustered index seek (and non-clustered index seek) occurs when the predicate contains one or more columns in the index. This allows the query processor to lookup the range of rows based on the index, without needing to scan.

Upvotes: 4

Miquella
Miquella

Reputation: 1084

A scan hits every entry in the index while a seek just traverses down to the item being queried, making the seek much faster. In general, scans should be avoided unless necessary.

Upvotes: 2

marc_s
marc_s

Reputation: 754488

Clustered index SCAN scans the whole clustered index (= data table) from beginning to end.

Clustered index SEEK only scans a (hopefully small) part / section of that index --> much faster!

Marc

Upvotes: 2

Related Questions