What is the difference between a clustered and non-clustered index?

A clustered index dictates the physical storage order of data rows in a table, meaning the data is the index. A non-clustered index, conversely, is a separate data structure that contains the indexed key columns and pointers to the actual data rows, which are stored elsewhere.

Mechanics and Why

A clustered index physically sorts the table's data rows on disk according to the index key. There can only be one clustered index per table, and it's typically created on the primary key due to its unique and frequently accessed nature. This physical ordering makes range scans (e.g., WHERE id BETWEEN 100 AND 200) and queries with ORDER BY clauses on the indexed column extremely efficient, as the database can read contiguous blocks of data. However, inserting data out of order can lead to page splits and physical reordering, potentially impacting write performance.

A non-clustered index is a separate, sorted structure (often a B-tree) containing the indexed key columns and a row locator (a pointer to the actual data row, or the clustered index key if one exists). A table can have multiple non-clustered indexes. They are ideal for quickly locating specific rows or for "covering queries" where all requested columns are part of the index itself, avoiding an additional lookup to the main data table. The trade-offs include additional storage for each index and increased write amplification, as both the table data and all relevant non-clustered indexes must be updated on data modifications.

Key Trade-offs

The choice between them involves a trade-off between read performance, write performance, and storage. Clustered indexes offer superior performance for range queries and ordered data retrieval with no extra storage cost, as the table is the index. Non-clustered indexes provide fast lookups for specific values and support multiple indexing strategies but incur additional storage and write overhead.

-- Example for a row-store database like SQL Server or MySQL
CREATE TABLE Products (
    ProductID INT PRIMARY KEY CLUSTERED, -- Defines the clustered index
    ProductName VARCHAR(255),
    CategoryID INT
);
CREATE NONCLUSTERED INDEX IX_Products_CategoryID ON Products (CategoryID);

In the interview, also mention…

While this distinction is fundamental to row-store databases like SQL Server and MySQL, modern columnar data warehouses (e.g., BigQuery, Redshift, Snowflake) handle data organization differently. They often use concepts like sort keys, clustering keys, or micro-partitions to achieve similar query performance benefits without explicit clustered/non-clustered indexes. For instance, Snowflake's clustering keys guide micro-partition organization. When choosing a clustered index, selecting an ever-increasing key (like an auto-incrementing ID or a timestamp) can minimize page splits and improve insert performance.