The short answer
The difference is where the table's rows are stored.
A clustered index decides the physical order of the rows in the table. The rows themselves are stored in the order of the index key. Because rows can only be in one order, a table can have only one clustered index. In many databases the is the clustered index by default.
A non-clustered index is a separate structure that holds the indexed column values plus a pointer to the actual row. The table's own order does not change. A table can have many non-clustered indexes.
An example with a phone book
- A clustered index is the phone book itself, sorted by surname. The data is the order.
- A non-clustered index is a separate list at the back, such as "people by city", where each entry tells you which page to turn to.
Trade-offs
- A clustered index is very fast for range queries on its key, such as dates between two values, because the matching rows sit next to each other.
- A non-clustered lookup needs an extra step to fetch the row from the table, unless the index already contains every column the query asks for. That case is called a covering index.
- Each extra non-clustered index slows down writes.
Terms differ between databases. The words "clustered" and "non-clustered" are used most in SQL Server. PostgreSQL, for example, has no permanently clustered index, but it has a CLUSTER command that reorders a table once.
Interview shortcut
> Clustered = the table is sorted this way, only one. Non-clustered = a separate lookup list, many allowed.