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.
A table scan is performed on a table when table does not have index on it. A query engine goes through each row in the table and if a row matches then it includes that into the result set.
A full table scan is performed when query is executed without WHERE clause.
Index scan is performed on indexed table, lets say if table has clustered index created on it but query is executed without using WHERE or HAVING clause, in that case query engine uses an index scan.
Lets take a look on table scan and index scan in SQL Server.
As you can see, here we have a table named CustomersData, and the total number of records table contains is 18,484.
select * from CustomersData
Let’s quickly check if table has any index and you can see it does not have any index as shown below.
sp_help CustomersData
Table Scan
Now, lets select the records from CustomersData table but before executing the query enable the STASTISTICS IO ON to see query’s IO information also enable the Execution Plan as shown below.
Now, lets execute the select query and see the execution plan and logical and physical reads IO information.
Let’s, click on execution plan tab and you can see query engine using table scan.
Now, click on Message tab to see the query’s IO information.
You can see, the logical reads is very high. A logical reads occurs every time the Database Engine requests a page from the buffer cache, and a each page is the size of 8 KB.
Now, let’s see what happens if we add the where condition in above query to see the specific customer details.
SELECT * FROM CustomersData WHERE CustomerKey =18028
You can see, again query engine uses table scan to select single records from table.
Now, let’s take a look on query’s IO information, and you can see logical reads is same for this query as well. That’s means it is reading all the pages and all the data from the table to get the single records from table.
Index Scan
Let’s take a look on index scan, for that we need to create a clustered index on table.
Here, we are going to create a clustered index on CustomerKey column.
CREATE CLUSTERED INDEX idx_customerkey ON CustomersData(CustomerKey);
You can quickly check whether the index is created on table or not.
SP_Help CustomersData
You can see the clustered index is created on CustomerKey column.
Lets, execute the select query to get all the records from table, means just execute the select query without using any condition.
SELECT * FROM CustomersData
You can see the query execution plan, and this time query engine uses index scan that is clustered index scan because table has clustered index created on CustomerKey column and query is executed without using any where clause means we have not used clustered index.
Let’s take a look on query’s IO information, and you can see the logical reads is still very high.
So, the conclusion is, when table does not have index on it, a query engine performs the table scan as it scans each rows in the table. And if query is executed without using WHERE clause, it performs full table scan.
While, if table has index, let’s say clustered index but query is executed without using that index or you can say without using WHERE clause or having clause then query engine performs Clustered index scan.
Also, note that in case of index scan – As we have created a clustered index on table so query engine performs clustered index scan, in similar way if a non clustered index is created on table then query engine performs non clustered index scan.
