Friday, 17 January 2014

Difference between Index Scan and Index Seek and 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 only 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. 

So from this example, you should have understood that, a query will result into an index seek, only if there is an index on the table to help they query to retrieve the data.

No comments:

Post a Comment

Paging in html table using javascript

I demonstrated how to use HTML tables on the client for a very simple client-side paging solution. I have heard from several people who p...