Where you need to be aware of using composite indexes in SQL Server

1, the index should be built on the high selectivity of the field (the key value of the number of records/total number of records), the more selective index, the better the effect, the greater the value, the unique index of the highest selectivity;

2, the order of the fields in the combined index, the higher the selectivity of the field ranked first, if the low-selectivity column on the leftmost side, it may be impossible to use the index situation.

3, when the Where condition contains two high-selectivity fields, you can consider creating an index, the engine will use two indexes at the same time (under the or condition, it should be said that the index must be separately built);

4. Do not repeat the creation of indexes that contain relationships with each other, such as index1 (A,b,c), Index2 (A, B), Index3 (a);

5, the combined index of the field is not too much, if more than 4 fields, generally need to consider splitting into multiple single-column index or simpler combination index;

6. Composite indexes are most useful when the where condition in a query statement uses the leftmost column of the composite Index.

7. Combined index increases the cost of updating data and increases the cost of storing indexes. In addition, in the DBMS query, the optimization scheme does not necessarily choose to him (it is possible to directly select a single-key query)

8, do not misuse the index. Because too many indexes not only increase the overhead of physical storage, it also increases processing overhead for insert, delete, and update operations, and increases the computational cost of the optimizer when selecting an index.

9. Too many indexes and inadequate, incorrect indexes are useless for performance.

10, the establishment of the index must be cautious, the need for each index should be carefully analyzed, to establish the basis.

Reference: SQL Server composite Index http://www.studyofnet.com/news/954.html

Where you need to be aware of using composite indexes in SQL Server