To effectively determine the appropriate indexes that should be created, you need to determine whether they'll actually be used by the SQL server. If an index isn't being used effectively, it's just wasting space and creating unnecessary overhead during updates.
The main criterion to remember is that SQL Server will not use an index for the more efficient bookmark lookup if at least the first column of the index is not included in a valid search argument (SARG) or join clause. Keep this in mind when choosing the column order for composite indexes. For example, consider the following index on the stores table:
create index nc1_stores on stores (city, state, zip)
Each of the following queries could use the index because they include the first column, city , of the index as part of the SARG:
select stor_name from stores where city = 'Frederick' and state = 'MD' and zip = '21702' select stor_name from stores where city = 'Frederick' and state = 'MD' select stor_name from stores where city = 'Frederick' and zip = '21702'
However, the following queries will not use the index for a bookmark lookup because they don't specify the city column as a SARG:
select stor_name from stores where state = 'MD' and zip = '21702' select stor_name from stores where zip = '21702'
For the index nc1_stores to be used for a bookmark lookup in the last query, you'd have to reorder the columns so that zip was first ”but then the index wouldn't be useful for any queries specifying only city and/or state . To satisfy all the preceding queries in this case would require additional indexes on the stores table.
You might think that the easy solution to get bookmark lookups on all possible columns is to index all the columns on a table so that any type of search criteria specified for a query can be helped by an index. This strategy might be somewhat appropriate in a read-only DSS environment supporting ad hoc queries, but not likely because many of the indexes probably still wouldn't even be used. As you'll see in the "Index Selection" section in this chapter, just because an index is defined on a column, it doesn't mean that the optimizer is necessarily always going to use it if the search criteria are not selective enough. Also, creating that many indexes on a large table could take up a significant amount of space in the database, increasing the time required to back up and run dbcc checks on the database. As mentioned earlier as well, too many indexes on a table in an online transaction processing (OLTP) environment can generate a significant amount of overhead during inserts , updates, and deletes and have a detrimental impact on performance.