0

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:

  • that it would take the first occurence
  • that it would take the last occurence
  • alphabetical order ascending taking the first
  • alphabetical order descending taking the first

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".

Vincent Verweij
  • 85
  • 1
  • 8
  • Instead of reengineering the synchronization of AX, couldn't you just compare the existing database with a synchronized database? – FH-Inway Aug 14 '15 at 07:51
  • You are right, but I thought doing it this way increases my knowledge about AX. But it seems that no one seems to know how this is determined (probably because this is core functionality). – Vincent Verweij Aug 14 '15 at 14:31
  • 2
    Have you considered the lowest or highest by id? – Jan B. Kjeldsen Aug 15 '15 at 18:07
  • I just checked and the recid is always added to the index with the lowest ID if two indexes are equal in size. I also shifted the place of the index to make sure that AX doesn't take the index that is listed first in the AOT. – Vincent Verweij Aug 17 '15 at 09:06
  • You can then answer the question yourself. – Jan B. Kjeldsen Aug 18 '15 at 08:33

1 Answers1

1

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.

Vincent Verweij
  • 85
  • 1
  • 8