3 4
Now that you know how to create indexes, let's look at how indexes are used. The fact that an index exists does not necessarily mean SQL Sever will use it. Whether SQL Server uses an index depends on the index and the SQL statement. In addition, if multiple indexes exist, SQL Server might have a choice of indexes that could be used. In this section, you'll see how SQL Server chooses indexes, and you'll learn how to use hints in order to specify which index to use. You'll also see how to use Query Analyzer to view a query execution plan.
When SQL Server Query Optimizer generates a query execution plan, it chooses an index based on which index will provide the best performance—usually the index that will use the fewest I/O operations and retrieve the least number of rows.
Although Query Optimizer usually chooses the most efficient query execution plan and access path for your query, you might be able to do better if you know more about your data than Query Optimizer does. For example, suppose you want to retrieve data about a person named "Smith" from a table with a column listing last names. Index statistics generalize based on a column. Suppose the statistics show that each last name appears three times on average in the column. This information provides fairly good selectivity; however, you know that the name "Smith" appears much more often than average. If you have an idea about how to better conduct an SQL operation, you can use a hint. A hint is simply advice you give to Query Optimizer specifying that it should not make the automatic choice.
Several types of hints are available—including join hints, query hints, and table hints—but here we are most interested in table hints. Table hints let you specify how the table is accessed. (Other types of hints are described in Chapter 35.) A table hint can be used to specify the following information:
Let's look specifically at a hint that specifies which index to use—an index hint. The following example illustrates an index hint in a T-SQL statement (to use the Region index for this query):
SELECT * FROM Customers WITH (INDEX(Region)) WHERE region = 'OR' AND city = 'Portland'
Note that the index hint is preceded by the keyword WITH. If you want to specify multiple indexes for SQL Server to use, list them in a T-SQL statement like this:
SELECT * FROM customers WITH (INDEX(Region, City, CompanyName)) WHERE region = 'OR' AND city = 'Portland'
An index hint parameter can be either an index name, as you have seen, or an index ID. There are also some special cases for hints, shown in the following table.
Hint | Result |
---|---|
INDEX(0) on a clustered table (clustered index exists on the table) | Forces a clustered index scan |
INDEX(1) on a clustered table | Forces a clustered index scan or seek |
INDEX(0) on a nonclustered table (no clustered index on the table) | Forces a table scan |
INDEX(1) on a nonclustered table | Interpreted as an error |
You can view the result of using a hint by executing your queries using SQL Server Query Analyzer.
In Chapter 13, you learned that Query Analyzer is a useful tool included with SQL Server 2000. We're going to look at this tool again to see how it can show us which index was used in a query execution plan. Query Analyzer can also be used for any of the following tasks:
To experiment with using Query Analyzer, load the following T-SQL statement into Query Analyzer:
SELECT * FROM customers WHERE region = 'OR' AND city = 'Portland'
Now examine the Estimated Execution Plan after choosing Display Estimated Execution Plan from the Query menu. You can see in Figure 17-23 that the index City is used.
Figure 17-23. The Estimated Execution Plan without a hint using the City index.
Now let's add a hint that instructs SQL Server to use the Region index. The query now looks like this:
SELECT * FROM customers WITH (INDEX(Region)) WHERE region = 'OR' AND city = 'Portland'
The Estimated Execution Plan for this query is shown in Figure 17-24. Notice that the Region index is now used.
Figure 17-24. The Estimated Execution Plan with the hint to use the Region index.
SQL Server Query Analyzer is quite useful and convenient for running SQL statements, not only by providing a GUI, but also by allowing you to parse and analyze the SQL statements. For operations that can be performed using scripting, you can save your work in Query Analyzer to a file by choosing Save As from the File menu.