Database Theory: Table Scan vs Index Scan vs Index Seek

Table Scan:

It scans every row for finding a particular node or data. If the planner fails to use a proper index, it has to do full Table Scanning.
Table Scan is a good, if our table has a less number of records.

For example, If Table has around 1000 records, Table Scan is faster because unnecessary Index Scanning operation is not required.
But, If the Table has a large number of records, Table Scan requires high CPU and other resource for scanning the whole table.

Index Scan:

It is scanning of each row, whenever you are retrieving more than 50% or 70% of the table data, internally planner performing full Index Scan.

Generally, Index pages and Data pages are stored separately.
This is also dependent on the ordering of the Index data because If matched key found from the top pages, Index Scan is not required.

The Row Lookups also involved in Index Scanning because matched Index key has to fetch data from the data page so Index Scanning is very costly for the big table.

Index Seek:

Index Seek is opposite the Table Scan and Index Scan.
It touches the only qualified row index rather than scanning whole Index Pages and after that, it fetches only qualified data page.

When we are fetching less number of records with the specific filter, the planner has to choose Index Seek to fetch and scan only qualified data.

Index Seek operation is much faster than Index Scan and also it does not require more CPU and I/O resources.

Anvesh Patel

Leave a Reply

1 Comment on "Database Theory: Table Scan vs Index Scan vs Index Seek"

Notify of
Sort by:   newest | oldest | most voted
MuKesh Singh

SQL – Index Scan Vs Table Scan
In absence of the indexes, A table scan will work on the data pages and this scanning starts from the first page to the last page for the data and in this way, scanned table stands on a heap and these data rows have their own pages. In the table scan, every row of data goes into the data scanning. If we have the huge data in the table and no index is not there then the query execution cost will increase due to high volume table scan.