Index Usage Criteria
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 ...
Get Microsoft® SQL Server™ 2000 Unleashed, Second Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
