INDEX Scan/Seek

One of my friends ask me about index scan/seek and when it happened.  This article give an idea related to index scan and index seek.

Index scan 

Index scan means the SQL server reads all rows on the table and returns those rows that satisfy the search criteria. All the rows of the left leaf of the index are scanned. That means all the rows of the index is searched instead of table directly. Please don't confuse with table scan. The time this takes is proportional to the size of the index. Generally the index scan is occurred when the query not finding a suitable index for a particular columns of the table.

When it used?

It is preferable when the table is very small and using index is over head

When a large percentage of the records match with searched criteria (10-15%).

 Index seek

The SQL server uses the B-Tree structure of index to seek directly to the matching records.  Time taken is only proportional to the number of matching records.

When it used?

Index seek is preferred when the number of matching records is proportionately much lower than the total number of records (greeter then the 50%).

Here is a general example where Index scan is used.

The Architecture

Table-A Contains 5 columns (Col-A, Col-B, Col-C, Col-D, Col-E)

The Index named Index-1 is activated on Table-A Col-A and Col-E

The Query

A SQL Query is fired with

WHEN Col-A='xxxx' AND Col-C='yyyyy'

The Output

As because the Index named Index-1 is on COL-A and COL-C and in the SQL statement we used COL-A and COL-C, it generate the Index Scan not Index Seek

            SQL Server 2008 introduces a new hint, the FORCESEEK hint that allows to "kindly"
            suggest Query Optimizer to use a seek operation instead of a scan.

.

            Example:

           

            SELECT *

     FROM  Table-A AS h

          INNER JOIN Table-B AS d WITH (FORCESEEK)

          ON h.ID = d.ID

     WHERE h.DueAmt > 100

          AND (d.OrderQty > 5 OR d.LineTotal < 1000);

           

Hope the article is quite informative and thanking you to provide your valuable time on it.

Posted by: MR. JOYDEEP DAS