However, adding the employeeID column to that index as follows could make the query a covered query because all the referenced columns would then be in the index:
CREATE INDEX customer_employee ON Orders(customerID, employeeID)
The same index will cover the query below because SQL Server will scan the leaf level:
SELECT customerID FROM Orders
WHERE employeeID= 5
Because row locators in the leaf level of a nonclustered index are actually the clustering keys if the table is clustered, you might have more covering indexes than you think. The following query is similar to a noncovered one I used earlier because it needs data from the customerID column, which wasn't among the declared index keys:
SELECT customerID, city FROM Customers
WHERE country = 'Brazil'
However, the plan for the above query shows that SQL Server performs a nonclustered index seek on the location_index index with no bookmark lookup:
|—Index Seek(OBJECT:([Northwind].[dbo]
.[Customers].[location_index]), SEEK:([Customers].[Country]=Convert([@1])) ORDERED FORWARD)
The query accessing customerID in this case is covered because SQL Server uses the clustered index key as the bookmark for all nonclustered indexes if the table has a clustered index. So this index really has three keys: Every index row contains the two defined keys of country and city, plus the clustered key (customerID) value that corresponds to that country and city combination. So any query that involves only the country, city, and customerID columns is a covered query.
Composites
Composite indexes can be useful structures even when they don't cover a query because they can help SQL Server pinpoint the location of the rows you've requested more precisely than can a single-column index. A composite index is most useful to a query when the search arguments (SARGs) in the query match the leftmost columns in the index key. For example, if an index has a composite key of last_name, first_name, that index is useful for queries such as WHERE last_name = 'Smith' or WHERE last_name = 'Smith' AND first_name = 'Bridgette'. But it isn't useful for a query such as WHERE first_name = 'Bridgette'.
Using an index is somewhat like using a phone book: You use a phone book as an index on last name to find the corresponding phone number. But the standard phone book is useless if you know only a person's first name—even if the first name is practically unique—because a first name might be located on any page. The phone book might have only one entry with that first name, but without an index sorted on first name, you'll have to scan the entire book to locate that name. Remember that the Northwind database's Customers table has a nonclustered index on country, city. The following query is looking for rows with the city value 'Seattle':
SELECT CompanyName, City FROM Customers
WHERE City = 'Seattle'
Although only one row has the desired value, SQL Server will perform a clustered index scan (table scan) unless a separate index on the city column also exists.
When you're building composite indexes, put the most selective columns leftmost in the key of nonclustered indexes. For example, an index on emp_name, emp_sex is useful for a SARG such as WHERE emp_name = 'Smith' AND emp_sex = 'M'. But if the index is defined as emp_sex, emp_name, it can't help with most retrievals. As the leftmost key, emp_sex can't narrow the search enough to make the index useful.
Be especially aware of this limitation when you're building indexes to enforce a primary key or unique constraint that's defined on multiple columns. SQL Server builds the index in the order that you define the constraint columns. So you need to adjust the column order in the constraint to make the index most useful to queries and list the most selective columns first; doing so won't affect the constraint's role in enforcing uniqueness.
Best Indexes
To create the best possible indexes for a table, you not only have to know your data so that you know which data is selective and which isn't, but you need to know what kinds of queries your users will be submitting. For example, will they always request exact values or will they sometimes be interested in ranges? Suppose you know that your users will be submitting queries involving both the product ID and the quantity values from the Northwind database's Order Details table. You might decide to build a composite index on those two columns. But which column should come first? ProductID has 77 values and is thus a bit more selective than Quantity, which has only 55 values. That difference isn't too great, however, so you'll need to consider other factors.
Various criteria can make a difference in which column you should list first. For example, suppose a user submits the following query:
SELECT * FROM [Order Details]
WHERE ProductID = 59
AND Quantity < 10
If a query contains both parts of a composite index as SARGs, which column you define first doesn't really matter as long as the SARGs are based on equality. For the above query, imagine that I created an index on Quantity first and then on ProductID. All the rows for Quantity 1 would be together for all the different product IDs, followed by all the rows for Quantity 2, for all the different product IDs. The 11 rows that satisfy my WHERE clause wouldn't all be together in the index's leaf level. But if, when I created the index, I specified the ProductID column first, all the rows for ProductID 59 would be together. And within all the 59s, the quantity values would be sorted, so SQL Server would store together all the rows that met my conditions.
The Last Piece
Here are a couple of basic rules about creating composite indexes. First, the most selective column should come first; second, columns that will be searched based on an equality comparison should come before columns that will be searched by range. In this and the past two articles, I've examined how indexes are organized and which indexes can be useful for which kinds of queries. But a missing piece that will help you grasp index and query tuning is an understanding of how the SQL Server query optimizer knows whether an index will be useful. In my next column, I look at how the optimizer decides whether to use any of the available indexes when choosing the best execution plan for a query.
End of Article
Prev. page
1
[2]
next page -->
