Indexes In Microsoft Dynamics AX

Today I would like to have a discussion about Indexes in AX. In my opinion, I think it is extremely important to create an architecture that will support the fast retrieving of records. In addition, there is no way we can provide a good service if we don’t provide a nice and easy to follow guide on indexes.  

So, basically indexes in databases are used to locate records and they are stored separately in the database. They contain a key that can be quickly located in the index, and this key will have a reference to a record.

For example, the let’s think on the SalesIdx index in the Sales Table. This table field index contains the SalesId of a Sales Order. Now, because the Sales order is unique and sequenced, an index will be used to quickly lookup the sales orders records through the reference.

Type of Indexes

Unique

A unique index is created based on a column; Microsoft Dynamics AX assures that no duplicate key values can occur in that same column. Also, when updating a column that contains a unique index will cause an error.

Non-Unique

Non-unique indexes are created for performance reasons. In other words, they give a fast way of retrieving data. For example, (and here we can think on the example above about the SalesIdx) instead of doing a full-table search of all the records in a table, and non-unique index will help us narrow down this search to a reference.

In general, indexes use system space and must be updated every time system data is created, edited, or deleted. I have seen indexes slowing down the updating process. However in most cases the overall performance improvement when selecting records far outweighs the performance loss when updating.

Microsoft recommends that when creating an index to give it a name that reflects the names of the fields on a table plus the suffix Idx. (i.e. SalesIdx)

Creating Indexes

Creating indexes is not very difficult at all. You just want to make sure that the fields you want to create the index on exist in the table (I guess this is obvious, but you’ll be surprise with some of the question I have been asked over the years). Anyway, the indexes can be unique or non-unique, and are based on a single column or multiple columns that exist within the table.

1. Locate a table in the AOT (This has to be a table in your Dev environment or some virtual machine AX)
2. Right-click the Indexes node in the table and select New Index. A new index Index1 is created.
3. Rename the index to the field name of the table/field you have chosen
4. Drag the field you have chosen to the index created in the step above node.
5. In the properties sheet for the created index node, set the property AllowDuplicates to No.

Just to make it more visually clear, please take a look at the following picture:

Take Care!