What Is an Index in SQL Server?

An index in SQL Server is a sorted copy of one or more columns, kept beside your table, with a pointer back to the full row. That is the whole idea. Everything else about indexing is a consequence of that one sentence, including why they make reads fast and writes slower.

The Back of the Book

Take a nine hundred page reference book and find every mention of one word. Without the index you read nine hundred pages. With it you turn to one page at the back, find the word, and it tells you exactly where to look.

The index is not the book. It is a second, smaller, sorted thing kept alongside it. Somebody had to build it, it takes up paper, and if a new edition adds a chapter the index has to be rebuilt. A database index is the same deal on all three counts.

What It Is Actually Worth

I built a table of 200,000 orders on SQL Server 2025 and counted the work. First without an index:

SET STATISTICS IO ON;
SELECT COUNT(*) FROM dbo.Orders WHERE city = 'Dublin';

Table 'Orders'. Scan count 1, logical reads 33517

33,517 reads to answer a question about 20,578 rows. SQL Server had no choice. Nothing told it where Dublin was, so it looked everywhere.

Then one line:

CREATE INDEX IX_Orders_city ON dbo.Orders(city);

Table 'Orders'. Scan count 1, logical reads 66

33,517 down to 66. Same query, same answer, five hundred times less work. That is the whole argument for indexing, and it is why the subject gets so much attention.

Clustered and Nonclustered

There are two kinds and the difference matters.

A clustered index is the table itself, held in that order. You get one, because rows can only be stored in one order at a time. Creating a primary key gives you one by default.

A nonclustered index is a separate structure: the columns you chose, sorted, plus a pointer back. You can have many. The one I created above is one of these.

A table with no clustered index is a heap, which means the rows sit in whatever order they arrived. This query tells you which you have:

SELECT i.type_desc AS structure
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID('dbo.Orders') AND i.index_id IN (0, 1);

Seek and Scan

Two words you will meet in every execution plan. A seek jumps straight to the rows it needs. A scan reads everything and throws away what does not match.

A scan is not automatically bad. Reading a whole small table is cheaper than jumping about. A scan on a large table where you asked for three rows is the thing to chase.

What an Index Costs

This is the half people skip, and it is why the answer to “should I add an index” is never simply yes.

Every index is a second copy of those columns, so it takes disk and memory. Worse, every INSERT, UPDATE and DELETE has to maintain it. Ten indexes on a table means one insert does eleven pieces of work. On a busy order table that is felt.

So an index is a trade. You are buying read speed with write speed and space. On a reporting table that is a bargain. On a table taking thousands of inserts a minute it may not be.

How to Choose One

Index the columns you filter and join on, not the columns you display. WHERE, JOIN and ORDER BY are where an index earns its keep. A column that only appears in the SELECT list rarely needs one of its own.

Column order matters in an index with more than one column, and it matters more than people expect. An index on city then amount helps a query filtering on city. It does almost nothing for a query filtering only on amount.

SQL Server will suggest missing indexes, and those suggestions are a starting point rather than an instruction. They are generated one query at a time, with no idea what else runs on that table. I have seen a server with forty suggested indexes on one table, most of them near duplicates of each other.

The practical rule I use is this. Start with none beyond the primary key, find the queries that actually hurt, and add indexes for those. Then check a month later whether they are being used at all, because a surprising number never are.

An index is not a setting you turn on, it is a second copy of your data that somebody has to keep current.

This post was rewritten from scratch in September 2026. The original, published on 2008-04-28, was a short announcement about something that no longer exists. The address is the same, the subject is now a basic idea worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.