As Microsoft says: “Generally, there are different methods for accessing the data in each table. If only a few rows with specific key values are required, the database server can use an index. If all the rows in the table are required, the database server can ignore the indexes and perform a table scan. If all the rows in a table are required but there is an index whose key columns are in an ORDER BY, performing an index scan instead of a table scan may save a separate sort of the result set. If a table is very small, table scans may be the most efficient method for almost all access to the table.”

Microsoft says: “The process of selecting one execution plan from potentially many possible plans is referred to as optimization. The Query Optimizer is one of the most important components of a SQL database system. While some overhead is used by the Query Optimizer to analyze the query and select a plan, this overhead is typically saved several-fold when the Query Optimizer picks an efficient execution plan.”

Engines

SQL Server’s Relational Engine is like the brains of SQL Server and the Storage Engine is like the muscles of SQL Server. They are useless without each other. The relational engine includes the optimizer, which is in charge of producing execution plans for queries. The storage engine performs the actual row operations based on the execution plan. Sometimes the optimizer’s instructions leave the storage engine with some room to maneuver. The storage engine will choose the best option from possibly several options.

The relational engine may declare to the storage engine any one of three possibilities. The storage engine may or may not have the opportunity to make a decision.

1. Table Scan –> allocation order scan
2. Index Scan Ordered:True –> index order scan (this is for clustered or nonclustered)
3. Index Scan Ordered:False –> allocation order scan OR index order scan