TABLE SCAN / INDEX SCAN , Index Seek
When SQL Server scans through all rows of a table one by one in order to find the appropriate rows.
For large tables, table scan can take a long time. But for very small tables, a table scan can actually be faster than an Index scan.
So if you see that SQL Server has performed a Table Scan, take a note of how many rows are in the table. If there are not many then in this case a Table Scan is better.
Means if you dont have any index on the table then Optimizer doesnt have a solution but to go for Table scan.
Now if there is an index available, why can’t an Index Seek be performed?
In some cases, such as if a huge quantity of rows need to be returned, it is faster to do an Table Scan than an Index Seek. Or it may be because the index is not selective enough. In any case, the Query Optimizer doesn’t think the available index is useful, other than for performing an Index Scan.
A table scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.
In an Index Scan is performed, all the rows in the leaf level of the index are scanned.
Essentially, this means that all of the rows of the table or the index are examined instead of the table directly.
Sometimes, the Query Optimizer determines that an Index Scan is more efficient than a Table Scan, so one is performed, although the performance difference between them is generally not much.
Generally speaking, an Index Scan or a Table Scan is almost the same thing, from a performance perspective. If you see any one of these in a query execution plan, the first thing you need to do is to see if there are few rows in the table. If so, then a scan is OK. Or, if many rows are being returned, then a scan is often faster than an Index Seek, and the Query Optimizer made the correct choice of selecting a scan. The only way to speed up this particular situation would be to find a way to rewrite the query in order to return fewer rows, assuming this is possible.
If the above two reasons don’t apply, then your next step would be to try to identify useable indexes to help speed the performance of the query, assuming that the current performance of the query is unacceptable, so that an Index Seek is performed instead of an Index or Table Scan.
SQL Server decides based on execution plan either to use Index Seek or Index Scan. User can not specify that. It also depends on how many numbers of records it is retrieving based on how many rows there are in table.
Add more index if possible (extra condition in where or join which is indexed). Also If you are adding too many indexes then it will decrease performance as SQL Server has to store and manage indexes.