Using Indexes

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.

Using Hints

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:

• Table scan In some cases, you might decide that a table scan would be more efficient than an index lookup or an index scan. A table scan is more efficient when the index scan will retrieve more than 20 percent of the rows in the table—such as when 70 percent of the data is highly selectable and the other 30 percent is "Smith."
• Which index to use You can specify a particular index to be the only index considered. You might not know which index SQL Server Query Optimizer would choose without your hint, but you feel the hinted index will perform best.
• Which group of indexes to select from You can suggest several indexes to Query Optimizer, and it will use all of them (ignoring duplicates). This option is useful when you know that a set of indexes will work well.
• Locking method You can tell Query Optimizer which type of lock to use when it is accessing the data from a particular table. If you feel that the wrong type of lock might be chosen for this table, you can specify that Query Optimizer should use a row lock, a page lock, or a table lock.

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.

Using 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:

• Performing SQL queries You can execute SQL statements and view the results with an easy-to-use GUI.
• Parsing queries By parsing an SQL statement without executing it, you can find and correct any errors.
• Displaying an estimated execution plan By displaying the execution plan, you can see how variations of the query affect the execution cost. This can be valuable in optimizing SQL statements by allowing you to rewrite your SQL statement and to see whether the cost changes.
• Performing index analysis An index analysis will tell you whether using an index will decrease the cost of a query's execution.

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.