A table can have multiple nonclustered indexes. Columns that are frequently used as search criteria, that can cover a query (the index itself contains the requested value), or that have distinct values are often appropriate for an index. If you build an index and it does not provide improved performance you can remove the index and build a different one, because an index is not a permanent part of the database. Nonclustered indexes must be updated every time a record in the table is inserted or deleted, or if the indexed column is modified. Because this takes time, an excessive number of indexes on a transactional database, such as the Eval database, can degrade performance. The following table lists table columns appropriate for nonclustered indexes and describes the benefits the nonclustered indexes provide.
| Table | Column | Benefit |
|---|---|---|
| Activity | ActivityTypeId | Provides faster search results for activities that share an activity type. |
| ActivityType | LateType | Provides faster lookup because activity type is frequently used as a search criteria. |
| Grade | Value | Provides faster search results because values tend to be distinct. |
| GradeScale | Name | Provides faster search results because values tend to be distinct. |
| Group | Name | Provides faster search results because values tend to be distinct. |
| Location | Name | Provides faster search result because values tend to be distinct. |
| Logon | No candidate | N/A |
| Person | FirstName + LastName | Provides faster search results because values tend to be distinct. |
| PersonActivity | ActivityId | Frequently involved in a search condition that returns a small result set. |
| PersonGroup | No candidate | N/A |