I found an interesting blog entry Difference b/w SELECT AND FIELDSELECT in Dynamics AX by Santosh, describing the difference between a select and a fieldselect.
It’s an interesting posting; Dynamics AX builds the sql statement in context of the used table and the fields used in the where condition. Btw. here are some more informations explaining how Dynamics AX will build the statement.
writing an normal x++ select statement
results to the translated sql statement as mentioned in the blog entry: (all fields of the table are listed)
writing a fieldselect statement
has the advantage of a change in the management of the table object, because there was no table handle defined.
but the result of the translated sql-statement doesn’t depends on the select / fieldselect……., it depends on the where condition of the select statement and (since DAX version 2009) on the cachelookup property of the used table.
selecting an unique record with a where condition on an unique index field (like RecId, AccountNum, DirPartyId) always results in a sql statement with all fields listed in, because the record comes out of or has to be inserted into the dynamics ax recordcache (since DAX version 2009, the record cache of the table must be activated)
writing a fieldselect statement where AccountNum == value
results a translated sql-statement with all fields listed
and writing a fieldselect statement where AccountNum like value
results a translated sql-statement with a fieldlist
the same behaviour will be reported when writing a select fieldlist statement with a where condition on a unique index field
all fields listed
and writing a select fieldlist statement without a where condition on a unique index field
with the fieldlist
In DAX3 and DAX4 every distinct select on a unique index field will be translated like a select *,
in DAX2009 every distinct select on a unique index field on a table with active cachelookup will be resolved in a select *
So, when Dynamics AX decides by itself how to build the select statement, why is this information of value? The answer is: 42
No, it’s the performance and the amount of memory used. Selecting single records by unique fields will always delivers the whole record. Selecting many records or joining multiple tables it’s important if the resultset contains 10 fields or 120 fields. Is the size of a record 500b or 12kB? And how many records will be transfered?