Database Fundamentals/Indexes

This lesson introduces indexes.

Objectives and Skills edit

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

Readings edit

  1. Wikipedia: Database index

Multimedia edit

  1. Microsoft Virtual Academy: Using DML Statements

Activities edit

Lesson Summary edit

  • 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]

Key Terms edit

clustered index
non-clustered index

See Also edit

References edit

  Type classification: this is a lesson resource.