How does SQL Server decide whether or not to use indexes?
SQL Server is a cost-based optimizer, not a rule-based system. Being cost-based, SQL Server can therefore be syntax-independent and literally cost each execution strategy based on the projected number and size of the results set. If you want to force table scans or index strategies, you can only guarantee their use by using “index hints.” This is generally not recommended, although at times it may become necessary. It is difficult to generalize and specify a basic set of rules under which the query processor will “always pick a table scan or index seek.” In general, the use of an index access strategy is favored over table scans unless the choice is very clear, for example, in the case when all rows are wanted. Table scans acquire shared locks, and can thereby greatly reduce concurrency (that is, multi-user access). That is why table scans are avoided whenever possible. Scan decisions are based on anticipated execution costs, so there is no “size” limit below which indexes are ignored.
