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

First, the use of composite index need to pay attention to the place

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.

second, the combination index in the query application example ( Combination Index (A, B, C) )

1, the following conditions can be used on the combination index query

• A>5
• A=5 and B>6
• A=5 and B=6 and c=7
• A=5 and B=6 and C in (2, 3)

2, the following conditions will not be used on the combined index query

• b>5--query condition does not contain the first column field of the combined index
• B=6 and c=7--reasons ibid.

3, the following conditions will be able to use the upper part of the combined index query

• A>5 and b=2--when a range query uses the first column, the query condition can only use the first column
• A=5 and B>6 and c=2--range queries use the second column, the query condition can only use the first two columns
• A=5 and B in (2, 3) and c=2--reason ibid.

An example of an application of a combined index in a sort ( Combination Index (A, B))

1, the following conditions can be sorted by the combination index

• ORDER BY a--first column sort
• A=5 ORDER by b--the first column is filtered and the second column is sorted
• ORDER by A desc, B desc --Notice that the two columns are sorted in the same order
• A>5 ORDER by a--data retrieval and sorting are all in the first column

2. The following conditions cannot be sorted with the combined index

• ORDER BY b--The second column in the index
• A>5 ORDER by b--range query in the first column, sorted in the second column
• A in (for) ORDER by b--
• ORDER by A ASC, B DESC --note that the two columns are sorted in different order at this time

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