SQL Server classics: clustered vs. non-clustered indexes

If you work with SQL Server databases and are interested in the performance of your databases and application using the database, understanding clustered and non-clustered indexes is essential.

Firstly, let's take clustered indexes. A clustered index defines how the actual table data is stored on disk. The table rows are physically ordered based on the clustered index key.

For example, assume you had a table like this:

CREATE TABLE dbo.Orders
(
    Id int NOT NULL,
    OrderDate datetime2 NOT NULL,
    CustomerId int NOT NULL,
    CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (Id)
);

In this case, the table is stored ordered by Id. There can be only one clustered index per table.

A non-clustered index is a separate structure that stores the indexed columns and a reference to the actual row.

CREATE NONCLUSTERED INDEX IX_Orders_OrderDate
ON dbo.Orders (OrderDate);

This index allows SQL Server to quickly locate rows by OrderDate, without scanning the whole table.

Conceptually, these are the most important ideas:

• Clustered index = the table itself is ordered on disk, defined by a key.
• Non-clustered index = a separate lookup structure pointing to the table.

Some practical notes:

• Choose a stable, narrow column for the clustered index (often an INT identity).
• Non-clustered indexes can be added for common query patterns.
• Too many indexes slow down inserts and updates.

Here is an example query using the non-clustered index:

SELECT *
FROM dbo.Orders
WHERE OrderDate = '2024-08-15';

SQL Server can use the index to find matching rows efficiently instead of scanning the table.

Getting the clustered index right is one of the biggest factors in overall table performance. Non-clustered indexes then support specific queries on top of that.

Hope this helps!