A clustered index determines the physical order of data rows in a table. The table’s rows are stored on disk sorted by the clustered‑index key, so there can be at most one clustered index per table.

A non‑clustered index is a separate structure that stores the index key and a pointer (row locator) to the actual data row. The data itself is not reordered, so multiple non‑clustered indexes can exist on the same table.

Clustered Index

  • Physical ordering:

    • Rows are stored in sorted order of the index key (e.g., primary key).

  • Good for range scans:

    • Queries like BETWEEN or > run fast because matching rows are stored contiguously.

  • One per table:

    • Only one physical order can exist, so only one clustered index is allowed.

Non‑Clustered Index

  • Separate index file:

    • Contains keys and pointers to data rows (often row IDs or clustered‑key values).

  • No effect on physical order:

    • The table can keep its existing order; the index is just an extra access path.

  • Multiple indexes possible:

    • You can create non‑clustered indexes on different columns to support various queries.

When to Use Each

  • Use clustered index on:

    • The primary key or the column most often used for range queries and sorting.

  • Use non‑clustered index on:

    • Frequently filtered columns (e.g., status, category, date) where you need fast lookups without changing the physical order.

For beginners, think of a clustered index like arranging books on a shelf in alphabetical order by title, while a non‑clustered index is like a separate card catalog that points to the books’ locations without rearranging the shelf.

Summary

A clustered index orders the physical data by the index key and is best for primary‑key and range access, while a non‑clustered index provides extra lookup paths without changing the physical order and supports multiple query patterns. Choosing the right type depends on query patterns, update frequency, and how you want the data physically stored.