Data access using indexes

Now that we have discussed how the Query Optimizer uses indexes to facilitate predicate pushdown and make queries more efficient, let’s explore how indexes are structured and why they are so important for query performance.

Before we begin discussing the structure of indexes, it’s worth understanding how data is stored and accessed in the SQL Database Engine. Data is stored on 8 KB pages. An object such as a table or an index is essentially a collection of pages, along with metadata that maps out the structure of the object. The SQL Database Engine uses a special metadata page called an Index Allocation Map (IAM) page to locate the pages in an object. IAM pages contain a list of all the pages in a database file that belong to an object. Each object will have at least one IAM page but depending on the size of the object and the file structure of the database, there may be more than one IAM page, forming a chain.

Tables that do not have a...