Indexes
An index lets the database find rows without reading the whole table, like the index at the back of a book. This lesson explains clustered and nonclustered indexes, how to choose columns to index, and the cost indexes add to writes.
- StartQuery arrivesSELECT Name FROM Customers WHERE Email = @email
- Step 2Optimizer checks indexesIs there an index that starts with Email?
- Step 3Index seekJumps straight to the matching entry in the sorted index
- Step 4Read the rowFetches only the matching row's data
- EndResult returnedMilliseconds instead of reading every row
Without a suitable index, SQL Server must scan the whole table, which gets slower as the table grows.
The idea in plain words
Tables are stored on disk in pages. To find customers with a given email, SQL Server would otherwise read every page. An index is a separate, sorted structure (a B-tree) on one or more columns that lets SQL Server jump to the right rows quickly.
A clustered index decides the physical order of the table itself, so there can be only one; the primary key gets one by default. Nonclustered indexes are extra sorted copies of a few columns with pointers back to the rows; a table can have many.
Example
-- Speed up lookups by email (and enforce uniqueness)
CREATE UNIQUE NONCLUSTERED INDEX IX_Customers_Email
ON Customers (Email);
-- Speed up "orders for a customer, newest first"
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderedAt
ON Orders (CustomerId, OrderedAt DESC)
INCLUDE (Total); -- covering: the query never touches the table
-- The query it helps
SELECT OrderedAt, Total
FROM Orders
WHERE CustomerId = @customerId
ORDER BY OrderedAt DESC;Choosing what to index
- Columns used in WHERE, JOIN and ORDER BY of frequent queries.
- Foreign key columns, which are used in joins and deletes.
- Put the most selective, equality-filtered columns first in a multi-column index.
- Check the execution plan in SQL Server Management Studio or Azure Data Studio to see whether a query seeks or scans.
Common mistakes
- Indexing every column. Each index slows INSERT, UPDATE and DELETE and uses storage.
- Wrapping an indexed column in a function, such as WHERE YEAR(OrderedAt) = 2026, which prevents a seek. Use a range instead.
- Leading with a wildcard, as in LIKE '%son', which cannot use the index order.
- Never looking at execution plans and guessing instead.
Interview question
What is the difference between a clustered and a nonclustered index?
A clustered index defines the physical order of the data rows, so the table itself is the index and there can be only one per table, usually on the primary key. A nonclustered index is a separate structure containing the indexed columns (plus any INCLUDE columns) and a pointer to the row, and a table can have many. Lookups through a nonclustered index may need an extra key lookup unless the index covers the query.
Want the full details? Read the official Microsoft documentation for this topic.