What is the difference between clustered and non-clustered indexes?

Clustered and non-clustered indexes serve distinct roles in database optimization. A clustered index directly determines the physical order of data rows in a table. When you create a clustered index, the database reorganizes the table’s data to match the index’s order, making it efficient for range queries or sorting operations. Each table can have only one clustered index because the data itself can’t be physically sorted in multiple ways. For example, if you create a clustered index on a UserID column, the rows will be stored on disk in ascending or descending order of UserID. This makes retrieving consecutive rows (like users with IDs 100–200) very fast.

In contrast, a non-clustered index is a separate structure that doesn’t affect the physical data order. Instead, it creates a sorted list of key values paired with pointers to the actual data rows. A table can have multiple non-clustered indexes, each optimized for different query patterns. For instance, a Users table might have a non-clustered index on an Email column to speed up login lookups. When querying by email, the database first scans the index to find the email’s location, then retrieves the full row from the main table. If the table has a clustered index, the non-clustered index points to the clustered index key; otherwise, it points directly to the disk location (heap).

The choice between the two depends on use cases. Clustered indexes excel for columns frequently used in range scans or as the primary access path (e.g., primary keys). However, frequent updates to the clustered key can lead to fragmentation. Non-clustered indexes are better for supporting ad-hoc queries on non-primary columns, but they add overhead during writes, as both the table and index must be updated. For example, adding a non-clustered index on CreatedDate helps filter records by date quickly but slows down insertions. Proper indexing requires balancing read performance gains against write costs and storage overhead.