, , , ,

Mostly people think that Scans are Bad and Seeks are Good.
Well this is only one side of the coin. Although Seeks are generally faster than Scans but there are some situations when Scans can be more advantageous than Seeks.
Index Scan
In index scanning, SQL Server scans all the data pages from the first data page to the last data page. Suppose there is an index existing in the table and the query is fetching large amount of data which is more than 50 percent of the data then the Query Optimizer would just fetch all the data pages to retrieve the desired resultsets. If there were no indexes in the table , then table scan would be performed which can be seen in the execution plan.

Index Seek
When SQL Server does a seek then it knows where in the index the data is going to be or when fewer number of rows such as only 10% of the whole data needs to be fetched, so it loads the index and directly goes to the part of the index that it needs and reads till the required data is fetched.

Most of the time query optimizer tries to use an Index Seek which indicates that it has found an useful index to fetch the desired resultset. But in case it fails to use the index or using index would not help the cause because the fetched number of records is almost around 90% of the whole data then it does Index scan.

Index scan is efficient if the table is small or most of the rows qualify for the recordset.

Subhro Saha