Indexes and Query Performance

Indexes are the single most important factor in SQL Server query performance. This lesson covers clustered and nonclustered indexes, execution plans, statistics, and common performance patterns.

---

What is an Index?

An index is a data structure (typically a B-tree) that allows SQL Server to find rows quickly without scanning every page in the table.

Think of it like the index in the back of a textbook — instead of reading every page, you look up the term in the index and jump directly to the right page.

---

Clustered Indexes

A clustered index determines the physical order of data in the table:

• A table can have only one clustered index
• The primary key creates a clustered index by default
• The leaf level of a clustered index contains the actual data rows
• Tables without a clustered index are called heaps

-- Clustered index is created automatically with PRIMARY KEY
CREATE TABLE Customers (
    CustomerId INT PRIMARY KEY,  -- clustered index
    Name       NVARCHAR(100),
    Email      NVARCHAR(200)
);

-- Or create explicitly
CREATE CLUSTERED INDEX IX_Customers_CustomerId ON Customers(CustomerId);

Choosing a Clustered Index Key

The clustered index key should be: