A clustered index determines the physical order of data in a table. The table data itself is stored in the leaf nodes of the index, meaning the table is the index.
Key characteristics:
Only one per table (since data can only be physically sorted one way)
Faster for range queries and sequential access
The primary key creates a clustered index by default in SQL Server
Inserting rows in non-sequential order can cause page splits and fragmentation
A non-clustered index is a separate structure from the table data. The leaf nodes contain the index key values plus a pointer (row locator) back to the actual data row.
Key characteristics:
You can have multiple per table (up to 999 in SQL Server)
Contains a copy of indexed columns plus a reference to the base table
Requires an extra lookup to fetch non-indexed columns (unless using a covering index)
Great for columns frequently used in WHERE clauses or JOINs