Information About Indexes

Important notes to remember on Indexes:

1.       Whenever you are using an index or index hint in the query, specify the fields in the where clause as defined in index otherwise SQL doesn’t use the index specified

2.       If a table index has been disabled by setting the index’s Enabled property to No, the select statement that references the index is still valid. However, the database can’t use the index as a hint for how to sort the data, because the index doesn’t exist in the database.

Unique and Non-Unique 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.

Note

A field of data type memo or container cannot be used in an index.

System Index

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.

If there are indexes on a table but none of them are unique, the runtime estimates the average key length of the existing indexes, chooses the index with the smallest key length and appends the RecId column to create a unique index.

Using Index Hints

To use index hints in queries you must first specify the use of hints on the server using the following procedure.

1. Open Start > Administrative Tools > Microsoft Dynamics AX Server Configuration Utility and select the Database Tuning tab.
2. Select Allow INDEX hints in queries and click OK.
3. A message box prompting you to restart the AOS service appears. Click Yes to restart the AOS service. Index hints won’t be enabled until the service is restarted.

Note

A wrong index hint can have a big performance impact. Index hints should only be applied to SQL statements that do not have dynamic where clauses or order by clauses, and where the effect of the hint can be verified.

When an index hint in a select statement refers to a non-clustered index and the WHERE clause contains only the fields that are found in a clustered index on the same table, the clustered index is used instead of the index specified in the hint.

For example, if you run sp_helpindex InventTable in SQL Server Management Studio, you see that the InventTable has a clustered index on the DataAreaId and ItemId columns and a non-clustered index on the DataAreaId, ItemGroupId, and ItemId columns.

Index name	Description	Key columns
I_175ITEMIDX	Clustered, unique, primary key located on PRIMARY	DATAAREAID, ITEMID
I_175GROUPITEMIDX	Nonclustered located on PRIMARY	DATAAREAID, ITEMGROUPID, ITEMID

In the following code the clustered index will be used instead of the non-clustered index specified in the index hint.

static void IndexHint(Args _args) {     InventTable inv;     ;     select * from inv index hint GroupItemIdx         where inv.ItemId == ‘B-R14′; }