Course contentsShow
← Computer Science
Lesson 1686 of 2,87238. Database Transactions and IndexingFree lesson

Clustered vs Non-Clustered B-Tree Indexes

Difference between indexes that determine row storage order versus secondary indexes with pointers.

Clustered vs Non-Clustered B-Tree Indexes

You'll learn the fundamental difference between indexes that physically organize table data versus indexes that simply point to it.

The Core Difference

When we build a B-Tree index on a table, it can work in two fundamentally different ways:

Clustered Index: The B-Tree structure determines the actual storage order of the table rows themselves. The leaf nodes of the B-Tree contain the complete row data, sorted by the indexed column(s). A table can only have one clustered index because the rows can only be physically arranged in one order.

Non-Clustered Index (Secondary Index): The B-Tree structure is separate from the table data. The leaf nodes contain pointers (or row IDs) that reference where the actual rows are stored. A table can have multiple non-clustered indexes because they're independent structures.

Library Analogy

Think of a library:

  • Clustered index: Books arranged on shelves by their call number. The index is the organization system. Finding a book by call number means walking directly to its shelf location.
  • Non-clustered index: A card catalog organized by author name. The cards point you to call numbers, then you must walk to the shelves (extra step).

Performance Implications

Clustered indexes are faster for range queries on the indexed column because related rows are physically adjacent—one disk read gets you multiple relevant rows.

Non-clustered indexes require an extra lookup step: first navigate the B-Tree, then follow the pointer to fetch the actual row data. However, they're essential for supporting multiple search patterns on the same table.

Key Takeaway: Clustered indexes physically organize table rows by the index key (one per table), while non-clustered indexes are separate structures that point to rows (multiple allowed).