Indexes & Performance

An index lets SQL Server find rows without scanning the entire table — similar to a book's index letting you skip straight to a page.

Creating an index

CREATE INDEX IX_Students_LastName
ON Students (LastName);

Now queries filtering or sorting on LastName are much faster on large tables.

Composite index

CREATE INDEX IX_Students_LastFirst
ON Students (LastName, FirstName);

Useful when you frequently filter/sort by both columns together, in that order.

Primary keys are indexed automatically

You don't need to manually index a PRIMARY KEY column — SQL Server creates a clustered index for it automatically.

Checking query performance

SET STATISTICS IO ON;
SELECT * FROM Students WHERE LastName = 'Ahmed';

Look at the "logical reads" in the Messages tab — fewer reads generally means a faster query.

When NOT to over-index

• Every index speeds up reads but slows down INSERT/UPDATE/DELETE (the index must be maintained too).
• Don't index columns you rarely filter or sort by.
• Don't index small tables — a full scan is already fast.