SQL Server Indexes

Question

What is the difference between clustered index scanning and clustered index search?

Answer 1

A clustered index scan is a scan of a table in a table with a clustered index. By default, the primary key is a clustered index, so basically a table with a primary key.

Cluster index scanning occurs when a predicate contains columns other than the primary key (and there is no other index available to satisfy the predicate).

( ) , . .

Answer 2

SCAN (= ) .

SEEK (, ) / → !

Answer 3

, , . , .

Answer 4

, , , , :

select Name from Table where Group = 42

Since the comparison is a direct value, it can easily be used to determine the part of the index in which the elements are located.

Scanning is used when the condition is more complex, so that each value in the index must be evaluated, for example, in a query like:

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

Since the condition uses the calculated value from the index, which cannot be easily used to extract part of the index, all elements must be evaluated.