What Is an Index?

Imagine the IPL database has 1,000,000 rows of player data and you ask for WHERE name = 'Hardik Pandya'. Without help, SQL has no idea where Hardik's row is, so it reads every single row until it finds him. An index is the help: just like the index at the back of a cricket book tells you "Hardik Pandya → page 3", a database index tells SQL exactly where the row lives, so it can jump straight to it in about 3 reads instead of 1,000,000.

Definition

An index is a separate, sorted data structure built on one or more columns of a table that stores the column values together with the location of each matching row, so the database can find rows without scanning the whole table.

• Separate data structure — the index is stored next to the table and takes its own disk space.
• Sorted — the values are kept in order (A→Z, 1→100), which is what makes fast searching possible.
• Location of each row — every index entry points to where the full row is stored (or, for a clustered index, is the row).
• Without scanning — the whole goal: read a few pages instead of all of them.

Note: An index speeds up reads (SELECT, WHERE, JOIN, ORDER BY) but slows down writes (INSERT, UPDATE, DELETE), because every change to the table must also update every index on it.

Data pages — where rows really live

SQL Server does not store rows "loose". It stores them inside pages.

A data page is a fixed-size 8 KB block inside the database's data file (.mdf). It is the smallest unit SQL Server reads from or writes to disk.

Every page has three parts:

Many pages together (1:150, 1:151, 1:152 …) make up the data file. Because the page is the unit of reading, performance is really about one number: how many pages a query must read. Fewer pages read = faster query.

Heap — a table with no index

A heap is a table that has no clustered index. Its rows are stored in pages in no particular order — usually just the order they were inserted.

SELECT *
FROM players
WHERE name = 'Hardik Pandya';

On a heap, Hardik could be on page 1:100, 1:101 or anywhere else, so SQL must do a full table scan: read page 1, page 2, page 3 … until the end. (It can't even stop at the first match — there could be a second Hardik.) With 1,000,000 rows that is thousands of pages.

Heaps are not always bad: inserting into a heap is very fast because SQL just appends the row wherever there is space. That's why heaps are used for staging tables that are loaded once and read in full.

B-Tree — the shape of an index

A B-Tree (Balanced Tree) is a hierarchy of pages — a root page at the top, intermediate pages in the middle and leaf pages at the bottom — where every leaf is the same distance from the root, so any value is reached in the same small number of hops.

To find id 11: root says "9–16 → B", B says "9–12 → Leaf 3", Leaf 3 contains id 11. Three hops. Because the tree is balanced, even 1,000,000 rows only need 3–4 levels.

Index types at a glance

Indexes are classified in three different ways — one index can belong to one group from each:

Each type gets its own topic next.

Example — see a scan become a seek (playground)

The playground runs on SQLite, whose plan command is EXPLAIN QUERY PLAN (SQL Server shows the plan graphically instead — see the Execution Plans topic). The idea is identical.

EXPLAIN QUERY PLAN
SELECT *
FROM players
WHERE name = 'Virat Kohli';

Now add an index and ask again:

CREATE INDEX IX_players_name ON players (name);

EXPLAIN QUERY PLAN
SELECT *
FROM players
WHERE name = 'Virat Kohli';

How it works: SCAN means "read every row". After the index exists, the optimizer switches to SEARCH … USING INDEX — it walks the B-Tree straight to "Virat Kohli". (The result also shows id, parent, notused columns; only detail matters.)

Common mistakes

• ❌ "Indexes make everything faster." ✅ They speed up reads on the indexed columns and slow down every write.
• ❌ Indexing a 6-row table and expecting a difference. ✅ Indexes matter on big tables; on tiny tables a scan is already instant.
• ❌ Thinking the index changes the query result. ✅ An index never changes what rows come back — only how fast.

Interview tip

Q: What is the difference between a heap and a table with a clustered index? A: A heap has no clustered index, so rows are stored in no order and every search is a full table scan (inserts are fast, though). A clustered index stores the rows physically sorted by the key inside a B-Tree, so a lookup by that key takes only a few page reads.