12. Indexes & Performance

The Problem: Table Scans

When you run a query with a WHERE clause on a column that has no index, the database engine performs a Full Table Scan. It starts at the very first row and reads every single row in the table from top to bottom, checking whether each one matches your condition. For a table with one million rows, that means examining one million rows — even if only one row matches.

Imagine looking up a name in a telephone directory that has not been sorted alphabetically. Your only option is to start at page one and check every entry until you find the name. In database terms, that is exactly what a table scan does.

The Solution: The B-Tree Index

An index is a separate data structure maintained by the database engine, built from the values of one or more columns. The most common type is a B-Tree index — a balanced tree that organises values in sorted order, allowing the engine to navigate to any value in a fraction of the time it would take to scan the whole table.

The analogy: a B-Tree index is the sorted alphabetical index at the back of a textbook. Instead of reading every page, you look up the keyword, find its page number, and jump directly there. A table with one million rows and a B-Tree index on the search column requires the engine to examine only about 20 comparisons to find any value — regardless of table size. That is the logarithmic efficiency of a balanced tree.

Creating an Index

Open SSMS, ensure you are in VoidX_Academy, and first check your Student table has enough rows to make performance visible. For this demonstration, the index lab uses a simulated million-row table. For learning the syntax, run the following on your own Student table.

First, run a query without any index and note the execution plan:

SELECT StudentID, FirstName
FROM Student
WHERE Email = 'kofi.mensah@ucc.edu.gh';

Press Ctrl + L before executing to see the estimated execution plan. With no index on Email, you will see a Table Scan operation in the plan — the engine reading every row.

Now create a non-clustered index on the Email column:

CREATE NONCLUSTERED INDEX IX_Student_Email ON Student (Email);

The naming convention IX followed by the table name and column name is standard and makes indexes easy to identify later. Run the same SELECT query again and check the execution plan. The Table Scan will be replaced by an Index Seek — the engine jumping directly to the matching row using the index structure.

Clustered vs Non-Clustered Indexes

SQL Server has two fundamentally different types of indexes and it is essential to understand the difference between them.

A Clustered Index determines the physical order in which rows are stored on disk. Think of it as the order the pages of the directory are sorted. A table can have only one clustered index because the data can only be physically sorted one way. When you define a PRIMARY KEY on a table, SQL Server automatically creates a clustered index on that column — which is why looking up a row by its primary key is always the fastest possible lookup.

A Non-Clustered Index is a separate structure that stores a sorted copy of the indexed column's values, each pointing back to the full row's location. A table can have many non-clustered indexes — one on Email, another on LastName, another on DeptID. Each one speeds up queries that filter or sort by its column.

CREATE NONCLUSTERED INDEX IX_Student_LastName ON Student (LastName);

CREATE NONCLUSTERED INDEX IX_Student_DeptID ON Student (DeptID);

Composite Indexes — Covering Multiple Columns

When queries frequently filter on two columns together, a composite index on both columns is more efficient than two separate single-column indexes. The order of columns in a composite index matters: the leftmost column is searched first.

If you frequently run queries that filter by DeptID and then sort by LastName, this composite index helps both operations at once:

CREATE NONCLUSTERED INDEX IX_Student_Dept_LastName ON Student (DeptID, LastName);

This index is useful for queries like WHERE DeptID = 1 ORDER BY LastName. The engine uses the index to find all rows for DeptID 1 — and they are already sorted by LastName within that group.

The Cost of Indexes — When Not to Over-Index

Indexes make SELECT queries faster, but they make INSERT, UPDATE, and DELETE operations slower. Every time a row is inserted or updated, SQL Server must also update every index defined on that table. A table with fifteen indexes takes fifteen times as much work to maintain per write operation compared to a table with no indexes.

The practical rule: index columns that appear frequently in WHERE clauses, JOIN conditions, or ORDER BY clauses. Do not index columns that are rarely searched. Do not index very small tables — a table scan on 50 rows is faster than navigating an index structure. And do not add duplicate indexes on the same column.

Viewing and Dropping Indexes

To see all indexes on a table in SSMS, expand your table in Object Explorer and open the Indexes folder. Every index is listed there with its type and columns. You can also query the system catalog:

SELECT
  name AS IndexName,
  type_desc AS IndexType
FROM sys.indexes
WHERE object_id = OBJECT_ID('Student');

To remove an index that is no longer needed:

DROP INDEX IX_Student_LastName ON Student;

How to Use the Index Performance Lab

The lab simulates a table called ServerLogs with one million rows. The Control Panel has two actions: a toggle to create or drop a B-Tree index, and a button to run a SELECT query searching for a specific error code.

Start without an index: click Run SELECT Query. Watch the terminal log on the right show that 1,000,000 rows were scanned and the query took approximately 2,845 milliseconds.

Now click Create Index. The log shows the CREATE NONCLUSTERED INDEX statement executing. Then run the SELECT query again. The log now shows 1 row scanned and the query completing in approximately 3 milliseconds — a 950x improvement from the same query on the same data, using the same search condition.

This is the single most visceral demonstration of why indexing matters. The query itself did not change. The data did not change. Only the presence of the index changed. Click Drop Index and run the query again to see the performance revert — reinforcing exactly what the index is contributing.