SQL Server - When SQL Server Performs a Table Scan and Uses and Index?

SQL Server accesses data in one the two ways:

1) Table Scan

• SQL Server reads all table pages.

2) Index

• SQL Server uses index pages to find rows.

By scanning all the data pages in a table called a table scan. When SQL Server performs a table scan, It:

1. Starts at the beginning of the table

2. Scans from page to page through all the rows in the table

3. Extracts the rows that meet the criteria of the query

By using indexes. When SQL Server uses an index, it:

1. Traverses the index tree structure to find rows that the query requests.

2. Extracts only the needed rows that meet the criteria of the query.

SQL Server first determines whether an index exists. Then the query optimizer  -the component responsible for generating the optimal execution plan for a query- determines whether scanning a table or using the index is more efficient for accessing data.

See Also
Clustered Indexes