Dynamics AX strange index functionality

At work we have a customer which doesn’t allow synchronizing. They have a database trigger which stops all Dynamics’ synchronization stuff.

Now we are moving on to a plan where the synchronisation is allowed but I have to make a list of all indexes that do not match between AX and the SQL database.

That for the story behind my question. The real question is the following: How does AX determine where it needs to add RecID to the index fields (in SQL only)?

Currently, I have a table with 5 indexes.
_{(source: smartus.be)}

_{(source: smartus.be)}

All these indexes (I know they are not optimal) have 1 field. The properties AllowDuplicates and Enabled are both set to Yes. At table level they properties PrimaryIndex and ClusteIndex are both empty and CreateRecIdIndex is set

But now if I look at SQL, I can see that one index always has DATAAREAID, field, RECID as its fields.
_{(source: smartus.be)}

The other indexes simply have DATAAREAID and the field specified in AX.
_{(source: smartus.be)}

When I delete all indexes and I synchronize again to test the case, all the indexes are rebuild and the same one has RECID again.

As I can see at property level of the index and the table’s fields, there is no indication why that index should have the RecID added. It’s the second index from the table.

So does anybody have a clue why AX is doing this? My guess is that AX wants one index to be unique what I understand, but why that specific index.

Regards, Vincent

Answer

You see this behavior, when:

you did not enable the CreateRecIdIndex property on the table
you did not specify a unique index

AX insists on having at least one unique index in order to be able to update a record. It selects the “smallest” index, then appends RecId. The spec states it takes the first.

If you do not create at least one unique index, Microsoft Dynamics AX creates one by combining the first index and the RecId.

Advertisement

Answer