Clustered vs Nonclustered: When an Index Makes a Query Slower

An index existing doesn’t mean using it is faster — sometimes it’s real, measured, and noticeably worse.

A nonclustered index, forced

CREATE INDEX ix_books_author ON books(author_id);

SET STATISTICS IO ON;
SELECT title, price FROM books WITH (INDEX(ix_books_author)) WHERE author_id = 25;

(100 rows affected)
Table 'books'. Scan count 1, logical reads 217, physical reads 0, read-ahead reads 1, ...

217 logical reads — worse than a plain scan of the whole table (40, from the previous lesson). WITH (INDEX(...)) forces SQL Server to use this specific index; without the hint, the optimizer actually declines to use it for this exact query, and it’s about to become clear why.

Why: a key lookup per row

ix_books_author only stores author_id and a pointer back to the clustered index — it doesn’t have title or price. For each of the 100 matching rows, SQL Server has to do a separate key lookup back into the clustered index to fetch those two columns. A hundred small lookups cost more total page reads than one sequential scan of all 40 pages — which is exactly why the optimizer, left alone, chose the scan:

SET STATISTICS IO ON;
SELECT title, price FROM books WHERE author_id = 25;

(100 rows affected)
Table 'books'. Scan count 1, logical reads 40, physical reads 0, ...

No hint, 40 reads — the optimizer already knew the plain scan beats the lookup-heavy index plan for this query, without being told.

The fix: a covering index with INCLUDE

DROP INDEX ix_books_author ON books;
CREATE INDEX ix_books_author_covering ON books(author_id) INCLUDE (title, price);

SET STATISTICS IO ON;
SELECT title, price FROM books WHERE author_id = 25;

(100 rows affected)
Table 'books'. Scan count 1, logical reads 4, physical reads 0, ...

4 logical reads — better than both the scan (40) and the lookup-heavy index (217). INCLUDE (title, price) stores those two columns directly in the nonclustered index’s leaf pages, without making them part of the seek key. The index now covers the query entirely — author_id to find the rows, title and price already sitting right there — so no key lookup back to the clustered index is needed at all.

Practice

SET STATISTICS IO ON;
SELECT title, published_year FROM books WHERE author_id = 25;

Table 'books'. Scan count 1, logical reads 40, physical reads 0, ...

DROP INDEX ix_books_author_covering ON books;
CREATE INDEX ix_books_author_covering ON books(author_id) INCLUDE (title, price, published_year);

SET STATISTICS IO ON;
SELECT title, published_year FROM books WHERE author_id = 25;

Table 'books'. Scan count 1, logical reads 4, physical reads 0, ...

SELECT i.name, s.user_seeks, s.user_scans, s.user_lookups
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.object_id = OBJECT_ID('books');

name                                       user_seeks  user_scans  user_lookups
------------------------------------------ ----------- ----------- ------------
PK__books__490D1AE1E186C24D                      32000           8            3
ix_books_author_covering                             1           0            0

Next: window functions — running totals and rankings without a self-join.