Indexes are the biggest lever you have on SQL Server performance. There are two fundamental kinds, and understanding the difference will change how you design every table.
The clustered index is the table
A clustered index stores the table's rows themselves, sorted by the index key, in a B-tree. That's why a table can have only one clustered index — rows can only be physically ordered one way.
CREATE TABLE dbo.Customers (
CustomerID int IDENTITY(1,1) NOT NULL,
Email nvarchar(254) NOT NULL,
Country char(2) NOT NULL,
CreatedAt datetime2 NOT NULL DEFAULT sysutcdatetime(),
CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomerID)
);
When you declare a PRIMARY KEY, SQL Server creates it as the clustered index by default (unless a clustered index already exists). A table with no clustered index is called a heap.
Nonclustered indexes are pointers
A nonclustered index is a separate B-tree containing the index key columns plus a row locator that points back to the full row:
- On a clustered table, the locator is the clustered index key.
- On a heap, it's a physical row identifier (RID).
CREATE NONCLUSTERED INDEX IX_Customers_Email ON dbo.Customers (Email);
A query like WHERE Email = @email can now seek straight to the right entry, then use the clustered key to fetch the remaining columns — a .
