SpDev Install free

SQL Server indexes explained: clustered, nonclustered and covering

How SQL Server indexes work, when the optimizer uses them and when it ignores them, key lookups, covering indexes and column order, with logical reads measured on real data.

By Areeb Ahmer, . 6 minute read.

An index is a sorted copy of some of a table’s columns, with a pointer back to the rest of the row. It lets SQL Server jump to the rows you asked for instead of reading the whole table. That one idea explains almost everything about indexing, including the cases where an index you created isn’t used.

All numbers below are logical reads (8 KB pages) measured with SET STATISTICS IO ON, against the 200,000-row dbo.Orders table of the SpDevDemo database.

The clustered index is the table

SELECT i.name, i.type_desc, ps.used_page_count, ps.row_count
FROM sys.dm_db_partition_stats AS ps
JOIN sys.indexes AS i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.object_id = OBJECT_ID('dbo.Orders');
name                   type_desc     used_page_count  row_count
---------------------  ------------  ---------------  ---------
PK_Orders              CLUSTERED     1340             200000
IX_Orders_OrderNumber  NONCLUSTERED  612              200000
IX_Orders_CustomerId   NONCLUSTERED  362              200000

A clustered index isn’t a copy: it is the table, with all columns, stored in key order (here, OrderId). A table has at most one. Without one, the table is a heap, an unordered pile of pages.

A nonclustered index is a separate, smaller structure: the key columns, sorted, plus the clustered key (OrderId) to find the full row. IX_Orders_CustomerId holds 200,000 pairs of (CustomerId, OrderId) in 362 pages, against 1,340 for the full table.

A good clustered key is narrow, unique, unchanging and ever-increasing: an INT or BIGINT identity is the usual choice. Every nonclustered index carries a copy of it, so a wide clustered key makes every index bigger.

Seek, scan and lookup

Count the open orders. There’s no index on Status yet, so SQL Server must read every row:

SET STATISTICS IO ON;
SELECT COUNT(*) FROM dbo.Orders WHERE Status = 'Open';
Table 'Orders'. Scan count 1, logical reads 1340

Add an index and run it again:

CREATE INDEX IX_Orders_Status ON dbo.Orders (Status);

SELECT COUNT(*) FROM dbo.Orders WHERE Status = 'Open';
Table 'Orders'. Scan count 1, logical reads 17

That’s an index seek: SQL Server navigates the index’s tree to the first 'Open' entry and reads only the 6,000 matching keys.

Now ask for a column the index doesn’t have:

SELECT OrderId, Total FROM dbo.Orders WHERE Status = 'Open';
Table 'Orders'. Scan count 1, logical reads 1340

Back to a full scan, with the index sitting there. To use it, SQL Server would seek the 6,000 keys, then do a key lookup into the clustered index for each one to fetch Total, at about three reads each: around 18,000 reads. Scanning the 1,340-page table is cheaper, so the optimizer scans. This is the tipping point. For a narrow index without the columns the query needs, it usually comes when the query returns more than a few percent of the table, often well under 5%.

Covering indexes

An index covers a query when it contains every column the query uses. Then there’s no lookup, and the tipping point disappears. Extra columns that you only return, not filter on, go in INCLUDE: they’re stored in the index’s leaf pages but not sorted by.

SELECT OrderId, OrderDate, Total FROM dbo.Orders WHERE CustomerId = 1;

CREATE INDEX IX_Orders_CustomerId_Covering
    ON dbo.Orders (CustomerId) INCLUDE (OrderDate, Total);

SELECT OrderId, OrderDate, Total FROM dbo.Orders WHERE CustomerId = 1;
SET STATISTICS IO OFF;
Table 'Orders'. Scan count 1, logical reads 1340     -- 20,000 rows: lookups would cost more, so it scans
Table 'Orders'. Scan count 1, logical reads 68       -- covered: one seek, no lookups

OrderId didn’t need including: as the clustered key, it’s in every nonclustered index already.

Column order in a composite index

An index on (Status, OrderDate) is sorted by Status first, then by OrderDate within each status. Like a phone book sorted by last name then first name, it helps you find “Ahmer, Areeb” and all the Ahmers, but not everyone called Areeb.

CREATE INDEX IX_Orders_Status_OrderDate ON dbo.Orders (Status, OrderDate) INCLUDE (Total);

SET STATISTICS IO ON;
SELECT OrderId, Total FROM dbo.Orders WHERE Status = 'Delivered' AND OrderDate >= '2026-10-01';
SELECT OrderId, Total FROM dbo.Orders WHERE OrderDate >= '2026-10-01';
SET STATISTICS IO OFF;
Table 'Orders'. Scan count 1, logical reads 21      -- seek on Status, then the date range within it
Table 'Orders'. Scan count 1, logical reads 654     -- no Status: no seek possible, a whole index is scanned

Without a predicate on Status, the index can’t be used to seek. SQL Server scanned the smallest index that had all the columns it needed, which here was the covering index from the previous section. Either way, it read every row.

The rule of thumb: columns compared with = first, then at most one column compared with a range (>, <, BETWEEN, LIKE 'abc%'), then nothing that needs to be sorted on. Selectivity matters less than people think; the predicates your queries actually use matter more.

What indexes cost

Every index is another structure to update. An INSERT into dbo.Orders now writes to the table and to every nonclustered index on it. Indexes also take space and memory, and each one is something the optimizer considers for every query. A table with fifteen overlapping indexes is a write-heavy system’s problem, not a solution.

Find indexes that cost more than they give back:

SELECT OBJECT_NAME(s.object_id) AS table_name, i.name AS index_name,
       s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.dm_db_index_usage_stats AS s
JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.database_id = DB_ID() AND i.type_desc = 'NONCLUSTERED'
ORDER BY s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) DESC;

An index with many updates and no seeks or scans is pure cost. The counters reset when the server restarts, so look at them after a representative period, including month-end.

SQL Server’s own suggestions

When the optimizer compiles a query and wishes it had an index, it records the wish:

SELECT d.statement AS table_name, d.equality_columns, d.inequality_columns, d.included_columns,
       s.user_seeks, s.avg_user_impact
FROM sys.dm_db_missing_index_details AS d
JOIN sys.dm_db_missing_index_groups AS g ON g.index_handle = d.index_handle
JOIN sys.dm_db_missing_index_group_stats AS s ON s.group_handle = g.index_group_handle
WHERE d.database_id = DB_ID()
ORDER BY s.user_seeks * s.avg_user_impact DESC;

Treat these as hints, not orders. Each suggestion is made for one query, ignores the indexes it would overlap with, never suggests a clustered index, and doesn’t know what the new index costs on writes. Merge similar suggestions into one index, and check the column order yourself.

Finding the index a slow query needs

  1. Get the actual execution plan of the slow statement (see how to read one).
  2. Look for scans that read many rows and return few, and for key lookups that execute thousands of times.
  3. Put the = columns of the predicate first in the key, then the range column, and the returned columns in INCLUDE.
  4. Measure logical reads before and after. If they didn’t fall, the index isn’t helping.

SpDev Pro does steps 1 and 2 on every statement of a profiled procedure. It reports scans that read far more rows than they keep (“reads 200,000 rows of dbo.Orders to keep 39”), key lookups by how many times they ran, and the CREATE INDEX with the right keys and INCLUDE columns. Then it compares the reads of your next run with the previous one, so step 4 is done too.