I am trying to find out how and when Dynamics AX adds a RECID to an index. I already found a few ways by reading the book "Inside AX 2009".
It adds a RECID to one index if none of the defined indexes are unique. It does this by estimating the average key length of each index, as stated on the microsoft site under section: System Index.
Now what I tried is look at the fields that are in the index and add a certain amount of bytes to each field. An integer in AX is an integer in SQL, so that field will be 4 bytes, an int64 in AX will be a bigint in SQL so 8 bytes, etc. For each field in the index I add this up and I get a total amount of bytes and the one with the lowest amount will be the one that gets the RECID field.
So far this has worked every time, but the issue raises when two indexes on one table have equal indexes, meaning both indexes have e.g. one field of type integer.
I already tried if both have the same amount of bytes, without results:
For the question: why do you want to know this The customer where I am working for hasn't allowed AX to synchronise the database, and I advised them to do so as AX is the master. They agreed and therefore I am making a report that shows which table mismatches with the database. The report works fine except from the recid thingy.
Therefore I am asking this question if anyone knows how to determine "the estimation of the average key length of an index".
Information about the indexes can be found on Microsoft's site:
https://msdn.microsoft.com/en-us/library/bb278358(v=ax.50).aspx
Here the following is stated:
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.
This can be determined by mapping the index fields to bytes as said in the question.
But if both indexes are equal in estimated average key length, AX will add the RECID field to the index which has the lowest index ID in the AOT.