Wednesday, February 27, 2013

Index Seek vs Index Scan


 Basically,
Index Scan:Index Scan retrieves all rows from the table
Index scan touches every row in a table whether or not it qualifies the cost is proportional to the total no of rows in the table 
Index is  nothing but scanning on the data pages from the first pages to the last page , if there is an index on a table and if the query is touching larger amount of data which means query is retrieving more than 50-90% of data and optimizer would just scan all the data pages to retrieve the data rows , if there is no index, then you might see a table scan (index scan) in the execution plan....
If there are no indexes or no useful indexes on a table then sql server has to scan all the records to satisfy querey condition.
An Index scan is complete scan of all pages in a non clustered index
Scan is efficient only if the table is small .

 Index Seek:Index seeks retrieves selective rows from the table
 index seek touches only rows that qualify the pages that contain the qualifying rows , the cost is proportional to the total no of qualifying rows and pages rather than the total no of rows in a table .


Thanks

No comments:

Post a Comment

https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context...