Indexes
Indexes are the biggest single lever for query speed. Like a book’s index, they let SQL Server jump straight to the rows it needs.
On this page · 5 min · Intermediate
On this page
- In simple words
- Learning objectives
- Explanation
- Syntax
- Code example
- Step by step
- Real-world use
- Common mistakes
- Best practices
- Remember this
- Practice exercise
- Mini quiz
- Related topics
- My notes
- Reading time
- 5 min
- Difficulty
- Intermediate
- Updated
- Oct 27, 2026
Related tutorials
In simple words#
An index is the index page at the back of a textbook. Instead of reading every page to find “photosynthesis”, you jump straight to page 142.
- Index
- A sorted structure that speeds up lookups.
- Clustered
- The table itself sorted by the key (one per table).
- Non-clustered
- A separate lookup list pointing to rows.
Learning objectives#
- Explain clustered vs nonclustered indexes
- Create composite and covering indexes
- Choose columns to index
- Understand the cost of indexes on writes
Explanation#
A clustered index defines the physical order of a table’s rows; a table has at most one, and the primary key creates it by default.
A nonclustered index is a separate sorted structure containing key columns and a pointer to the row. A table can have many.
Column order matters in composite indexes: an index on (CustomerId, OrderDate) helps queries filtering on CustomerId, but not ones filtering only on OrderDate.
INCLUDE adds extra columns to the index leaf level so a query can be answered entirely from the index — a covering index. Every index speeds up reads but slows inserts, updates and deletes.
Syntax#
CREATE NONCLUSTERED INDEX IX_Table_Col
ON dbo.Table (KeyCol1, KeyCol2)
INCLUDE (OtherCol);
Code example: Speeding up “orders for a customer”#
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders (CustomerId, OrderDate DESC)
INCLUDE (Status);
SELECT OrderId, OrderDate, Status
FROM dbo.Orders
WHERE CustomerId = 1
ORDER BY OrderDate DESC;
OrderId OrderDate Status 103 2026-02-10 Pending 101 2026-01-05 Delivered -- Execution plan: Index Seek (NonClustered) [IX_Orders_CustomerId] -- No Key Lookup and no Sort operator are needed.
This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.
Step-by-step explanation#
- The key (CustomerId, OrderDate DESC) lets SQL Server seek directly to customer 1’s rows, already in date order.
- Status is included, and OrderId is the clustered key (always present in nonclustered indexes), so the index covers the query.
- The plan shows an Index Seek with no lookup or sort.
Real-world use case#
An orders page that is slow for large customers often needs exactly one well-chosen composite, covering index.
Common mistakes#
Unused indexes waste space and slow every write. Index for your real queries.
An index on (OrderDate, CustomerId) cannot seek on CustomerId alone.
Best practices#
- Index foreign keys and frequent filter columns
- Put equality columns first in composite keys
- Use INCLUDE to cover important queries
- Review missing and unused index reports periodically
Remember this#
Index = textbook index page for a table.
One clustered index per table; many non-clustered.
Indexes speed reads, slow writes slightly.
Practice exercise#
Your task: Design an index for: SELECT Name, Price FROM Products WHERE Category = @c ORDER BY Price.
Show solution
CREATE NONCLUSTERED INDEX IX_Products_Category_Price ON dbo.Products (Category, Price) INCLUDE (Name);
Mini quiz#
How many clustered indexes can a table have?
Want more? Practise all SQL Server MCQs · SQL Server interview questions
My notes#
Saved in this browser.