Depending on its type, an index is stored with the data or separate from the data.
In systems without indexes, all data retrieval must be done by using table scans.
In a table scan, all of the data in a table must be read and compared with the requested data.
Table scans are generally avoided
because of the amount of I/O that is generated by this operations
- scanning large tables could take a long time and eat up a lot of system resources.
By using an index,
* you can greatly reduce the number of I/O operations,
* speeding up access to data as well as freeing up system resources for other operations.
A database index is organized in a B-tree structure.
Each page in an index is called an index page or an index node.
The index structure begins with a root node at the top level.
The root node marks the beginning of the index;
it is the first data accessed when a data lookup occurs.
The root node contains a number of index rows.
These index rows contain a key value and a pointer to an index page (called a branch node),
as illustrated in Figure 17-1.
This configuration is necessary because in an average-size data table,
an index consists of thousands or millions of index pages.
By starting at the root node and traversing the branch nodes,
SQL Server can zoom in on the data you want.
Microsoft SQL Server 2000 Administrator's Companion eBook