Sign in to save your place.

MODULE 10 OF 12

Indexes & Performance

Speed up queries with indexes; understand clustered vs nonclustered and execution plans.

What is an index?

An index is a structure that helps SQL Server find rows faster (like a book index). It can speed up WHERE, JOIN, and ORDER BY on the indexed columns. Indexes use storage and can slow down INSERT/UPDATE/DELETE, so create them on columns you search or sort by often.

Clustered vs nonclustered

TypeDescription
ClusteredOne per table. Stores the actual row data in index order. The table is physically ordered by the clustered index key (often the primary key).
NonclusteredSeparate structure that points to the data. You can have many nonclustered indexes per table.

CREATE INDEX & execution plans

Use CREATE INDEX to add a nonclustered index: CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId). Use execution plans (e.g. "Include Actual Execution Plan" in SSMS) to see how SQL Server runs the query and whether indexes are used.

Key Takeaway

Index columns used in WHERE, JOIN, and ORDER BY. One clustered index per table; add nonclustered indexes where they help. Check execution plans to verify.

How indexes help

Without an index: full table scan. With an index: seek or scan on the index, then lookup rows. Execution plan shows Index Seek (good) vs Table Scan (often costly on large tables).

CREATE INDEX example

-- Nonclustered index on CustomerId (speeds up WHERE CustomerId = ... and JOINs)
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId ON Orders(CustomerId);

-- Composite index for filtering + ordering
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate ON Orders(CustomerId, OrderDate DESC);

Practice

Create a nonclustered index on a column you often filter or join on. Run a query before and after; use "Include Actual Execution Plan" and compare (look for Index Seek vs Table Scan).

Quiz

1. How many clustered indexes can a table have?

One
Two
Unlimited
Zero

Correct! A table can have at most one clustered index.

A table has at most one clustered index; the data is stored in that order.

2. What do execution plans show?

How SQL Server executes the query (e.g. index seek vs table scan)
The list of tables in the database
Backup history
User permissions

Correct! Execution plans show how the query is run and which operators are used.

Execution plans show how SQL Server runs the query—e.g. Index Seek, Table Scan, and cost estimates.

October 2026: columnstore and Query Store

A clustered index is the row order of the table. A nonclustered index is a second structure for lookups. A columnstore index stores columns in a form that favors big scans and aggregates. It is a poor match for "fetch this one order by id" as the only access path. Columnstore exists on SQL Server 2022 and 2025. Confirm the edition before you promise an online rebuild.

Query Store records plans and runtime statistics so you can see a regression after an update. It is not a backup of the table. Turn it on for a database whose plan changes you will actually look at.

ALTER DATABASE Practice SET QUERY_STORE = ON;

Try this. In a practice database, enable Query Store if your edition allows it, run one query twice, and find that query in the Query Store reports. If the option is missing, write down the edition from SERVERPROPERTY('Edition').

Knowledge check

What is Query Store for?