In RDBMS table scan event occurs while querying data from table of a database .
When SQL Server need to process more data or entire data in the table to get the result set of given queries criteria that is called TABLE SCAN. Table scan is good at some time and bad at some times we will see cases.
Consider that we are maintaining set of files related to our office somewhere in an unorganized manner. At that time if someone asks us to take a particular file, we will be searching every file and after getting that particular file, we will be giving as appropriate.
Consider the same situation where you are maintaining every files in an organized manner. If some one asked for some files without searching we will go that file and then we will give.
While comparing above process obviously the second situation will take less time compared to first situation.
The same process is happening in SQL Server. When a query is submitted to SQL Server, it attempts to determine the best way to execute a query, generating what is called a “Query Execution Plan”. Use Ctrl+M to turn on the actual execution plan in SSMS and then execute the query.
When SQL Server has to search through all of the data in a table to satisfy a query, require more I/O and also takes longer to process. This is something you will notice with an application that grows over time.
Solution for Table Scan
Consider that we are having Database called Vembu and inside that there is a table named Employee .That table is having column ID,First Name,Last Name ,DOB,Year of Join,Blood Group.
Use Vembu;
Select * from Vembu.Employee ;
When we want to select every data from Employee table then SQL Server Query Optimizer will choose table scan option .Because it should have processed every Records in the table. You can see that in Query Execution plan window.
Consider that we are not having any Indexes for Employee table . At that when we try to query the table to select only for particular entry at that there is no need for table scan (No need to process all the records in the table).
Use Vembu ;
Select * from Vembu.Employee where ID BETWEEN 100 and 200 ;
There is no index created for this table so it will process every records in the table to check condition and give the result set. The Query process time will increase or hang when that table having millions of data.
So to resolve this We need to create ClusteredIndex or NonClustered Index for that table(Employee). Whenever we are setting one column as a Primary Key the Cluster Index is automatically created by SQL Server for that table according to primary key columns.
When we set ID column as an Primary Key for Employee table then than the records in the tables are sorted by ascending by default we can order desc as well the records in the tables are arranged in a manner physically on disk .
Now if we try to query same ID 100 to 200 the query optimizer will use Cluster Index to get the result set. It will start search for records once it reaches ID 200 it will not continue search .it will stop at that point and return result set. So the process time for query execution is less and also resource uses also less (disk I/o).
A table can have only one clustered index because the records can physically sorted in only one order.
Consider that now we need to records which satisfies the below condition.
Use Vembu ;
Select * from Vembu.Employee where Year of Join < 2014 ;
We can create nonclustered index on a table for the column Year of Join it will logically sort the records.
But we cannot create nonclustered index for every column it will decrease performance.
While running the above query in SSMS by going through Query Execution Plan we can know what index we need to create for the better execution of the above query.
In Query Execution Plan window it will show Missing Index if we need to create any index for the running query . We can create that query after the the query execution will become faster.
So, to avoid Table scan at most places we need to create indexes and it will improve query execution and it will use less resources.
Follow our Twitter and Facebook feeds for new releases, updates, insightful posts and more.
