Adding an index on fields in a SQL Server table

Primary keys
SQL Server Indexes
SQL Server tables

Answer Wiki


Yes, you can.

You should consider creating indexes on columns that are frequently used in the WHERE ( and ORDER BY, and GROUP BY ) clause of queries.

Fields with low selectivity (such as gender) are not good candidates for indexing.

Discuss This Question: 1  Reply

Send me notifications when members answer or reply to this question.
  • Kccrosser
    Carlosdl is correct - you should create indexes on columns commonly referenced in the WHERE clauses. A couple of points to consider. 1. While fields with low selectivity may not be "great" for indexing, sometimes an index on such a field may significantly improve performance. Let's say you have a Personnel table containing a lot of person descriptive (name, dob, sex, ...) columns (and a lot of data) and you do a bunch of queries that include sex as a criteria. Searching a condensed index on Sex may be quicker than retrieving the data records to test the value. (i.e., an "index full scan" may be a lot faster than a "table full scan") 2. It is nearly impossible to have "too many" indexes. When in doubt, add an index. Ok - there are exceptions. If your application is a high-volume data capture application, with relative few queries on the data, then you may need to minimize indexes for database write performance. However, for the typical application where queries occur 10 times (or more) frequently than the insert/update transactions, you should not be afraid to add indexes. 3. Creating "effective" indexes is a bit of an art form. While you can just define an index on each individual column, composite indexes (with multiple columns in the index) can be MUCH more effective. However, you need to understand the queries that will be used, and the order of the columns in the index can be VERY important. 4. For maximum performance, often a "covering index" can be used. This is an index where the actual field being requested is included in the index itself. Consider the query: ** select c from mytable where a=<value> and b=<value> You could put individual indexes on "a" and "b", but the most effective index may be a composite index on (a,b,c). NOTE that the field to be returned MUST be the last field in the composite index, and this usually only works when you only need one field to be returned from a single table query. When the database engine is able to use a covering index, it may be able to return the answers without even touching the actual underlying data table(s). This can be 10x or 100x faster than with individual indexes and table fetches. 5. When defining any composite index, try to ensure that the order of the columns in the index are from the "most discriminant" (i.e., the column with the greatest number of distinct values) to the "least discriminant" (i.e., the column with the fewest distinct values). This can help the query optimizer choose the best index(es) for a query, and even speed up the use of the index.
    3,830 pointsBadges:

Forgot Password


Your password has been sent to:

To follow this tag...




Share this item with your network: