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

10 comments:

  1. Hi Venkat,
    You are doing a great job.I was looking for videos on SQL SERVER which gives clear concept,i found your's. I would really appreciate if you post videos on SSIS and SSRS.

    Thanks,
    Zoobi

    ReplyDelete
  2. Hi Venkat,

    Really you all videos as superb, it would be great if you post videos related to MSBI.

    Regards
    Pooja

    ReplyDelete
  3. Hi Venkat,

    Your Videos are really good.It would be very helpful if you post videos on WCF,Silverlight,SSRS.



    With Regards,
    Dwarak

    ReplyDelete
  4. Hi Venkat,
    I am a new vie software developer and i found that the vedio created by you and your way of explain the concept is very good and best i am still learning things by you.
    Thanks a lot


    With Regard,
    Gulshan Kumar Lodhi

    ReplyDelete
  5. Thanks very much for your super work. Do you have MSBI video?

    ReplyDelete
  6. Hi Venkat,
    thanku so much for your tutorial. i have no words for you, you are most amazing teacher i had ever experienced.
    with regards,
    Manish Jawla

    ReplyDelete
  7. i am having one doubt in transactions. In one of my interview ,i was asked to tell another command to commit a transaction without using commit keyowrd

    ReplyDelete
  8. Thank you so much. this is really very helpful to me.

    ReplyDelete
  9. Hi Venkat, I am sujit and thank you so much. One question in my mind is : How to update ID column according to DeptID asc order, for example, i have 5 records, DeptID is 4,5,7,3,2 and want to update id column from deptID i.e. 2,3,4,5,7.

    ReplyDelete
  10. This is the best resource of learning database I ever seen.
    Thanks Sir for your great work.

    ReplyDelete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us