Setting up composite indexes

About this task

Composite indexes are a feature of Microsoft™ SQL server databases that improve the retrieval times of records by allowing data to be retrieved without the need to open the record table. To create a successful composite index, you need to pay attention both to the types of fields that you add, and to the ordering of those fields.

Key fields form the main part of an index and are important for index structure. They determine the order that the data is stored, and are used to filter and sort data. Included fields are used to cover queries that are not covered by the key fields. They are not used to sort or filter data, but are used to retrieve data more quickly.

You should create index entries for the most common terms to be associated, in the order that they are most likely to be run. For example, if you would like to add composite indexes to improve the performance of running queries, align the fields in your index with the discriminators that are used in the import specifications.

Procedure

1. Select Tools > Database Design > Composite Indexes.

2. Choose the Record Type that you would like to create a composite index for.

3. Select Add.

4. For each index combination that you would like to add for this record type:

   1. Select Add.

   2. On the Key Fields tab, choose the Key Fields to use and click OK. To remove Key Fields, select one or more fields and click Remove.

   3. On the Included Fields tab, choose the Included Fields to use and click OK. To remove Included Fields, select one or more fields and click Remove.

   4. Click OK. The index is created but can be deleted.

5. Continue to create indexes for other record types by repeating steps 2 - 4.

6. Click Close.