Back to the list of all SQL Server 2000 / 2005 Interview Questions
|
|
What is the difference between Index Scan, Table Scan, and an Index Seek?
|
Index Scan
Index Scan scans each and every record in the index. Table Scan is where the table is processed row by row from beginning to end. If the index is a clustered index then an index scan is really a table scan.
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Hence, a scan is an efficient strategy if the table is small.
Index Seek
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.
Example
I have an employee table as shown in the diagram below. EmployeeId is the primary key. We have a clustered index on the employeeId column.
|
|
Query 1 :
Select
* from Employee
where FirstName='Ben'
|
|
Query 2 :
Select *
from Employee where
EmployeeId=2
|
Query 1 will do an Index scan (Table Scan) to retrieve the record as there is no Index on the FirstName column.
Query 2 will do an Index seek to retrieve the record as there is an Index on the EmployeeId column.
|
Back to the list of all SQL Server 2000 / 2005 Interview Questions
|