What is an Index?
An index is a table-specific database structure that speeds the retrieval of rows from a table. Indexes are used to improve the performance of data retrieval and occasionally to ensure the existence of unique records.
Once created, indexes are managed automatically by the DBMS every time a record is inserted, updated, or deleted. Within Microsoft Dynamics AX, an index can be enabled or disabled. When an index is disabled, it’s deleted from the database and rebuilt if it’s enabled later.
An index is defined by one or more fields. The system attempts to order the index according to the first field, and if there is more than one record with the same value in this field, the sorting conflict is resolved by looking at the next field and so on.
When selecting table fields for an index consider the following:
- Fields that are often searched by a range.
- Fields that frequently participate in joins.
- Fields that are frequently used to order or group a result set.
Note: However, it’s common to have at most a few indexes enabled because every insert, update, and delete causes each index to be updated and can affect performance.
Types of Indexes:
There are two types of indexes: unique and non-unique.
Whether an index is unique is defined by the index’s AllowDuplicates property. When this property is set to No, a unique index is created. The database uses the unique index to ensure that no duplicate key values occur. The database prevents you from inserting records with duplicate key values by rejecting the insert.
Setting the index’s AllowDuplicates property to Yes creates a non-unique index. These indexes allow you to enter duplicate values for the indexed fields and are used for performance reasons.
Microsoft Dynamics AX requires a unique index on each table so if there are no indexes on a table or all the indexes are disabled, a system index is automatically created. The system index is created on the RecId and DataAreaId fields if the DataAreaId field exists. Otherwise the system index is created on the RecId field. You can see system indexes in the database but they aren’t visible in the AOT.
What is the difference between index and index hint ?
Using “Index“: when you add the statement “index MyIndex”, the Axapta kernel will add an “ORDER BY” with all the fields of the index.
Using “Index hint”: when you add the statement “index hint MyIndex”, the Axapta kernel will add a statement to instruct the database to use that index and no other one.
Using “index hint”, you take away the control of which index to use from the database optimizer. So, if there may be a better index, the database will not use it.
Adding the “index” statement to an Axapta select, it does NOT mean that this index will be used by the database. What it DOES mean is that Axapta will send an “order by” to the database.
Adding the “index hint” statement to an Axapta select, it DOES mean that this index will be used by the database (and no other one).