Sunday, February 16, 2014

Index Scan VS Index Seek

Index Scan
In index scan, SQL Server scans all the data pages from the first data page to the last data page. For example 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 result sets.

      Also if there is no indexes in the table, then SQL server will automatically do table scan. So table scan and index scan is same but while doing table scan you moved into one more level of data to retrieve original data.

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 result set. 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 record set.

No comments:

Post a Comment