Database Fundamentals/Indexes

This lesson introduces indexes.

Objectives and Skills

Objectives and skills for the indexes portion of Microsoft Exam 98-364 Database Fundamentals include:^[1]

Understand data storage
- Understand indexes
  - Understand clustered and non-clustered indexes and their purpose in a database

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed.^[2]
Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.^[3]
In a non-clustered index, the physical order of the rows is not the same as the index order. The indexed columns are typically non-primary key columns used in JOIN, WHERE, and ORDER BY clauses. There can be more than one non-clustered index on a database table.^[4]