Database Fundamentals/Indexes

From Wikiversity
Jump to navigation Jump to search

This lesson introduces indexes.

Objectives and Skills[edit | edit source]

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 | edit source]

  1. Wikipedia: Database index

Multimedia[edit | edit source]

  1. Microsoft Virtual Academy: Using DML Statements

Activities[edit | edit source]

Lesson Summary[edit | edit source]

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

clustered index
non-clustered index

See Also[edit | edit source]

References[edit | edit source]

Nuvola apps edu miscellaneous.svg Type classification: this is a lesson resource.