Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
The Size of Index Keys
SQL Server can utilize an index in two ways—it can scan the index, or it can seek for a particular value or set of values within an index. Index seeks are much faster than index scans. The query engine decides whether SQL Server should perform an index scan or index seek; however, you can affect this decision by the way you write queries.
Regardless of the method used, the leaner indexes are easier to run through; therefore, indexes built on short keys (integer values, rather than character values) usually perform better. On the other hand, sometimes you can benefit from the covering indexes—indexes that contain all columns contained in a WHERE clause.
When selecting the columns to be indexed, consider the concept of index selectivity. Index selectivity is the number of distinct values in the index key. More selective indexes yield better performance improvements, whereas if you have only a few distinct values in an index key (for instance, only 1s and 0s), the index isn't likely to speed up your queries. Occasionally you can also benefit from non-selective indexes—for instance, if you can satisfy the entire WHERE clause with a covering index containing multiple keys.