Dec 22, 2013 by Dick Wenning Category: AX2012 R2, Hotfix, SQL 1 comment

Table inheritance in AX 2012 R2 is different on SQL level in case you compare it to AX2012.
For people that don’t know what table inheritance is look at the next picture.

image001
The Table company info is in AX 2012 stored in 3 different tables, DirpartyTable, OMInternalOrganization and finally CompanyInfo. The have all the recId and are inner joined.
These inner joins are not fast (performance penalty) so Microsoft decide to put them all in the same table (R2)
So fine, but last months I see cases at customer site or in the communities about unretrieved values etc.
The next picture is an example of it.

image002

How can this happen?

  • A model store with new fields is published to production environment.
  • A model with a new field is published to de production environment.

If we look on SQL on these fields we see an value NULL. NULL is something difference than 0 (zero) or ” (empty string)
Microsoft decided to use null values on table inheritance tables to reduce database storage. There is nothing wrong with that. But when we add a column MS should do correct defaulting.
In the next picture from SQL you see null on the end of the fields, this is normally not the case with Dynamics AX tables on SQL

image003

So how do you solve this. In the next example I added an field on DirOrganizationBase,  So all derived table records needs to be updated

image004

In case of a number or enum it is SET YOURFIELD = 0

Comments1

Sorry, the comment form is closed at this time.