Adding a point query with different indexing requirements

The index choice for the range query on price produced a clear performance choice when all possibly useful indexes were considered. Now, assume this query also needs to run against titles:

select price
from titles
where title = "Looking at Leeks"

You know that there are very few duplicate titles, so this query returns only one or two rows.

Considering both this query and the previous query, Table 8-3 shows four possible indexing strategies and estimate costs of using each index. The estimates for the numbers of index and data pages were generated using a fillfactor of 75 percent with sp_estspace:

sp_estspace titles, 1000000, 75

The values were rounded for easier comparison.

Table 8-3: Comparing index strategies for two queries

Possible index choice

Index pages

Range query on price

Point query on title


Nonclustered on title Clustered on price

36,800 650

Clustered index, about 26,600 pages (135,000 *.19)

With 16K I/O: 3,125 I/Os

Nonclustered index, 6 I/Os


Clustered on title Nonclustered on price

3,770 6,076

Table scan, 135,000 pages

With 16K I/O: 17,500 I/Os

Clustered index, 6 I/Os


Nonclustered on title, price


Nonmatching index scan, about 35,700 pages

With 16K I/O: 4,500 I/Os

Nonclustered index, 5 I/Os


Nonclustered on price, title


Matching index scan, about 6,800 pages (35,700 *.19)

With 16K I/O: 850 I/Os

Nonmatching index scan, about 35,700 pages

With 16K I/O: 4,500 I/Os

Examining the figures in Table 8-3 shows that:

The best indexing strategy for a combination of these two queries is to use two indexes:

You may need additional information to help you determine which indexing strategy to use to support multiple queries. Typical considerations are: