Wednesday, May 11, 2016

SQL - Index Scan Vs Table Scan

As we know that data is very important thing for any organisation and this data is always stored on disk based storage devices and there data is known as as blocks of data. These blocks are accessed in their entirety, making them the atomic disk access operation. 
Indexing is a way of sorting a number of records on multiple fields. However, defining two different locations on two different hard disks for index data and table data can decrease/eliminate the problem of increased cost of time. 

To avoid the table scans, it would be an ideal standard or milestone that every table should have an index.

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.

Index Scan-If data table contains the indexes then index scan will occur on the data pages which starts from the first page to the last page. In the index scanning process or index search, it always seeks only index table because they have their own index pages.
If we are talking about the performance then table scan and index scan will run in the same manner. 

Index Seek- If table having an index which plays a very important role for finding a match for the given query. In this case, only data with pointers matching the index need to be retrieved. A seek uses the index to pinpoint the records that are needed to satisfy the query. 
A CLUSTERED index scan is the same as a table scan. It is important to remember that depending on the rest of the query a table/index scan may not actually scan the whole table - if the logic allows the query plan may be able to cause it to abort early. 

No comments:

Post a Comment