The single most important factor in determining the effectiveness of an index is the uniqueness of index values. A unique index value is found faster than a value with multiple index occurrences. You can have multiple occurrences of index values if you have more than one row with the same data value in a column, or if you have a computed column (such as IHASH) whose values share the same result, or if the index is hashed.
Note that we are talking about unique index values, not data values. An index value may or may not be the same as a data value. Long text columns that are hashed when indexed have a higher probability of unique data values creating non-unique index values. An index loses its effectiveness as duplicate values increase and R:BASE must make more reads and comparisons.
Indexed columns also affect performance when adding or changing data in a table. Indexed columns must be updated when a row is added or when the index column value is changed. The number of indexed columns in a table affects the speed with which rows are added or changed in the table. Take this into account when defining indexes and constraints. It is faster to load and change data on a table with one indexed column than on a table with seven indexed columns.
