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 13-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 13-3: Comparing index strategies for two queries**
Possible index choice		Index pages	Range query on price	Point query on title
1	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
2	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
3	Nonclustered on title, price	36,835	Nonmatching index scan, about 35,700 pages With 16K I/O: 4,500 I/Os	Nonclustered index, 5 I/Os
4	Nonclustered on price, title	36,835	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 13-3 shows that:

For the range query on price, choice 4 is best; choices 1 and 3 are acceptable with 16K I/O.
For the point query on titles, indexing choices 1, 2, and 3 are excellent.

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

Choice 4, for range queries on price.
Choice 2, for point queries on title, since the clustered index requires very little space.

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

What is the frequency of each query? How many times per day or per hour is the query run?
What are the response time requirements? Is one of them especially time critical?
What are the response time requirements for updates? Does creating more than one index slow updates?
Is the range of values typical? Is a wider or narrower range of prices, such as $20 to $50, often used? How do different ranges affect index choice?
Is there a large data cache? Are these queries critical enough to provide a 35,000-page cache for the nonclustered composite indexes in index choice 3 or 4? Binding this index to its own cache would provide very fast performance.
What other queries and what other search arguments are used? Is this table frequently joined with other tables?