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:

Property	Why
Narrow	Key is stored in every nonclustered index — smaller is better
Unique	Avoids adding a hidden uniqueifier (4 bytes)
Static	Changing the key requires physical row movement
Ever-increasing	Avoids page splits (e.g., INT IDENTITY)

Tip: An INT IDENTITY column is the ideal clustered index key in most cases.

---

Nonclustered Indexes

A nonclustered index is a separate structure that points back to the base table:

• A table can have up to 999 nonclustered indexes
• The leaf level contains the index key columns plus a row locator (clustered key or RID)
• Nonclustered indexes improve read performance but slow down writes

-- Single column
CREATE NONCLUSTERED INDEX IX_Customers_Email ON Customers(Email);

-- Composite index (multiple columns)
CREATE NONCLUSTERED INDEX IX_Customers_Name_Email ON Customers(Name, Email);

Included Columns (Covering Indexes)

Include non-key columns in the leaf level to avoid lookups to the base table:

CREATE NONCLUSTERED INDEX IX_Customers_Email_Inc
ON Customers(Email)
INCLUDE (Name);

A query that only needs Email and Name can be fully satisfied from the index — no need to read the base table. This is called a covering index.

---

Index Types Summary

Index Type	Description
Clustered	Determines physical row order; leaf = data rows
Nonclustered	Separate B-tree; leaf = key + row locator
Unique	Enforces uniqueness (can be clustered or nonclustered)
Filtered	Index with a WHERE clause — smaller and more focused
Columnstore	Column-based storage for analytics and data warehousing
Full-Text	Searches text content within columns
XML	Indexes XML data type columns
Spatial	Indexes GEOGRAPHY and GEOMETRY columns

---

Execution Plans

An execution plan shows how SQL Server executes a query:

Viewing Execution Plans

-- Estimated plan (without running the query)
SET SHOWPLAN_XML ON;
GO
SELECT * FROM Customers WHERE Email = 'test@example.com';
GO
SET SHOWPLAN_XML OFF;
GO