What is the difference between a clustered and a non-clustered index?
Quick Answer
A clustered index determines the physical order of the rows in the table — the leaf level of the index is the table data — so there can be only one. A non-clustered index is a separate structure; its leaf level holds the index key plus a pointer (the clustered key, or a RID for a heap) back to the row, and a table can have many.
The clustered index is the table — its leaf level holds the actual rows in sorted order by the clustered key, so you only get one per table. A non-clustered index is a separate copy of just the key columns, sorted, with a pointer back to the row. You can have lots of them. The practical consequence is the key lookup: if a query uses a non-clustered index but needs a column that isn't in it, SQL Server has to jump back to the clustered index for every matching row, and that gets expensive. You avoid it by including the needed columns in the index so it “covers” the query. Also, because the clustered key is stored inside every non-clustered index as the locator, you want it narrow and increasing — an int identity, not a random GUID.
| Clustered | Non-clustered | |
|---|---|---|
| How many per table | One | Up to 999 |
| Leaf level contains | The actual data rows, sorted by the key | Index key + row locator |
| Affects physical order | Yes | No |
| Extra lookup? | No — you're already at the data | Sometimes a key lookup to fetch non-indexed columns |
| Storage | No extra copy of data | Separate structure, extra space |
Key points:
- A table with a clustered index is a clustered table; without one it's a heap. The primary key creates a clustered index by default (you can override).
- A non-clustered index seek that needs columns it doesn't contain does a key lookup into the clustered index for each row — fine for a few rows, expensive for many. Fix by
INCLUDE-ing those columns (a covering index). - The clustered key is duplicated into every non-clustered index as the locator, so a narrow, static, ever-increasing clustered key (like an
int/bigintidentity) is preferred; a wide or random key (like a random GUID) bloats every index and causes page splits.
CREATE TABLE dbo.Orders
(
OrderId INT IDENTITY PRIMARY KEY, -- clustered by default: narrow, increasing
CustomerId INT NOT NULL,
OrderDate DATETIME2 NOT NULL,
Total DECIMAL(12,2) NOT NULL,
Status TINYINT NOT NULL
);
-- avoids a key lookup for: SELECT CustomerId, Total FROM Orders WHERE Status = 1
CREATE NONCLUSTERED INDEX IX_Orders_Status
ON dbo.Orders (Status) INCLUDE (CustomerId, Total);Practise the whole thread the way an interviewer walks it — each question builds on the last.
- What is an index in SQL Server?
- What is the difference between a clustered and a non-clustered index?You are here
- When should you create an index?
- Can indexes hurt performance?
- What is an execution plan in SQL Server?
- How do you optimise a slow SQL query?