Microsoft® SQL Server™ 2005 Administrator's Companion

How to Optimally Take Advantage of Indexes

In order to take advantage of indexes, the index key must be referenced in the WHERE clause of your SQL statement. In a multicolumn index, the leading edge of the index must be supplied in the WHERE clause of your SQL statement. This must be the leading edge of the index since the data in the index is sorted on the first index key value and then subsequent key values.

For example, if an index is created on the columns last_name and then first_name, the data is sorted first by last_name and then within each last name the first_name data is sorted. Figure 12-3 shows an example that illustrates this point.

Figure 12-3. Example of using an index.

Since the values in the second key column in the index are scattered ...