Some or all of this information may be specific to Microsoft SQL Server 2000, and is not guaranteed accurate for other RDBMS implementations.

A table scan is, generally speaking, the worst case scenario for retrieving a specific row or sequence of rows which make up a small percentage of your table. When SQL Server performs a table scan, it runs through every entity in the table and compares it to the provided search arguments. If a query requests most or all of the rows in a table, a table scan is usually employed anyway, and that is just fine (there's nothing wrong with reading every row in arbitrary order if that's what you want).

Now, through the magic of analogy, I shall demonstrate how a table scan can be acceptable and mention a possible alternative.

Let's pretend that you are the owner of a business, and you keep all of your invoices of purchased materials in boxes in various places around your house. Suddenly one of your many hundreds of suppliers goes out of business, and you want to know what materials you have purchased from them, so you decide to look at all the invoices for that company.

SELECT * FROM suppliers WHERE supplierID = 712

Let us further pretend that you keep the invoices unsorted in random locations around your house: a box under the kitchen sink, a box or two in the garage, a few loose pages under your pillow, etc. In order to find all of the invoices you are interested in, you will need to go to every box/pile of invoices, and check every single one to see if it matches your now ex-supplier. Now, assuming that there are hundreds of thousands of boxes scattered around, each containing several hundred invoices, and you are only looking for a few of them, you are going to look quite insane (and probably will be by the time you have completed this task).

The above example could be made much more manageable through the use of directories known as indexes (regular or clustered) which are described in those nodes.

However, if you instead wanted to know how much total money your company has spent on materials, you would go around your house and scoop up all of the invoices as you come to them.

SELECT SUM(orderCost) FROM suppliers 

This second case is an example where attempting to use an index would be just a waste of time, and yet another reason to question your sanity.
Beginning with version 7.0, SQL Server has used an Index Allocation Map to improve the performance of table scans by preparing a list of disk addresses so that it can perform more efficient sequential I/O operations and benefit from readahead. This is one way in which a table scan can acutally be a better choice than an index lookup. Another situation in which a table scan is more practical than an index seek is when the data contains few distinct values, for example a boolean flag; a column with only two possible values is not an optimal case for a search tree.

Log in or register to write something here or to contact authors.