Table Scan vs Index Scan vs Index Seek

A table scan or an Index scan are performed in response to any query when SQL Server has to scan the data or index pages to find the requested records.
The SQL Server optimizer’s job is to choose the best way to execute a query. The optimizer uses indexes to improve query execution time.

Table Scan:
A table scan is performed on a table when table does not have index on it.
While performing table scan, query engine goes through each row in the table and if a row matches the query condition, then it includes that into the result set.
A full table scan is performed when query is executed without WHERE clause.
For a small table, a query engine can load all the data in a one-shot but from a large table it is not possible i.e., more IO and more time will be required to process those large data.
A table without Cluster Index is called Heap in SQL.

Index Scan:
Index scan is performed on indexed table, let's say if table has clustered index created on it and the query is executed, in that case query engine uses an index scan.
Table scans iterate over all table rows, Index scan iterate on all index items, when item index meets search condition, table row is retrieved through index.
Usually index scan is less expensive than a table scan.
Index scan significantly reduces the number of I/O read operations; it often outperforms a table scan.

Index Seek:
SQL Server automatically chooses Index seek instead of Index Scan if we are querying a table with large number of records with selected rows in the result set.
If we apply where condition, index seek is used if not index scan is used, this applies when we have many records in table.
Index scan means it retrieves all the rows from the table and index seek means it retrieves selective rows from the table.
Index Scan touches every row in the table it is qualified or not, the cost is proportional to the total number of rows in the table.
Index Seek only touches rows that qualify and pages that contain these qualifying rows. The cost is proportional to the number of qualifying rows and pages rather than the total number of rows in the table.

An index seek does not scan entire index, instead it navigates the B-tree structure to find one or more records quickly.

Create new table without Primary Key and run select.

Add Primary Key and run the select.

Insert Records into table and run select.

Apply where condition, where you have data and Primary key.

Hope you find this Article interesting for short bits around the technology follow me on LinkedIn at www.linkedin.com/in/anveshsalla