Note on Index scan, Index seek, Table scan

Data required for executing a query can be accessed in three different ways.

1. Index Scan

2. Index Seek

3. Table Scan

Let us assume a query like below,

SELECT emp_name FROM EMP 

WHERE emp_id = 2210 ;

And also assume that, all the explaination which i am giving below is valid for a query of above kind only.

If there is no index defined on any columns of the table which are involved in the where clause, then the method chosen will be a Table Scan.

If the column "emp_id" has an index on it, then the data required for the above query is accessed by the method of Index Scan. 

One should aware of the fact that, if there is an index defined on a column "x", then all the values of that column are stored in that index.

So, in the case of above query, index was defined on "emp_id". But we want to fetch the values of column "emp_name". So with the help of index on "emp_id", the storage engine can identify the address of a particular set of or single page in which the record related to condition "emp_id=2210" satisfies. 

But in-order to fetch the values of the column emp_name, it needs to do the bookmark look-up (i.e, going to that particular page of the original table).

Then what's and index seek?

If suppose the query is in the below way,

SELECT emp_id FROM EMP 

WHERE emp_id=2210;

In this case the method chosen is Index Seek.

Meaning, if the column on which the index is defined is selected (needs to be fetched), then there is no need to perform any bookmark look-up because if there is an index on a column, then all the values of that column will be stored in that index.

Thank you for reading!

Any issues, queries and comments are encouraged with respect.