Microsoft® SQL Server 2012 Unleashed

Understanding Index Structures

When you run a query against a table that has no indexes, SQL Server has to read every page of the table, looking at every row on each page to find out whether each row satisfies the search arguments. SQL Server has to scan all the pages because there’s no way of knowing whether any rows found are the only rows that satisfy the search arguments. This search method is referred to as a table scan.

A table scan is not an efficient way to retrieve data unless you really need to retrieve all rows. The Query Optimizer in SQL Server always calculates the cost of performing a table scan and uses that as a baseline when evaluating other access methods. The various access methods and query plan cost analysis are discussed ...