What is Table Scan?

Table Scan comes to picture when you search for data in a table and you table does't have any index created on it or your query does't take advantage of any existing index of table.
In normal condition Table Scan is not good but in some circumstances its good to use.

A table scan is the easiest and simplest operation that can be performed against a table. It sequentially processes all of the rows in the table to determine if they satisfy the selection criteria specified in the query. It does this in a way to maximize the I/O throughput for the table.

A table scan operation requests large I/Os to bring as many rows as possible into main memory for processing. It also asynchronously pre-fetches the data to make sure that the table scan operation is never waiting for rows to be paged into memory. Table scan however, has a disadvantage in it has to process all of the rows in order to satisfy the query. The scan operation itself is very efficient if it does not need to perform the I/O synchronously.

Now lets understand what exactly happens during Table Scan

Reads all of the rows from the table and applies the selection criteria to each of the rows within the table. The rows in the table are processed in no guaranteed order, but typically they are processed sequentially.

When Table Scan isn't Bad thing

If you are retrieving every row from a table, and plan on using it, it doesn't really matter that you are doing a table scan because there is really no other way to do it. Another time that it doesn't matter so much is when there is very little data in a table, like a type table or a small lookup table, because SQL Server can intelligently cache data when it needs to, and when there is very little data it can actually degrade performance some when it does have an index because of the overhead of maintaining the index. Striking a good balance of indexing vs. non-indexing is a skill that will come with time and experience.

Ex:

SELECT * FROM Employee
WHERE WorkDept BETWEEN 'A01'AND 'E01'
OPTIMIZE FOR ALL ROWS

References:
http://strangenut.com/blogs/dacrowlah/archive/2008/02/24/what-is-a-table-scan.aspx

Compiled By: Rajesh Rolen