Select statement on field

A field select is a special select statement in X++. The description is available on msdn, but what is actually executed on SQL Server? Can the performance be improved? Let's analyse it.

Hints
Run field select statement on several tables and track actual T-SQL statements in SQL Server Profiler. You can also jump to the conclusion section.
Solution

Case 1. Table with a surrogate key as a primary index

Let's run field select on AgreementHeaderDefaultHistory table:

static void selectFieldTest1(Args _args) { AgreementHeaderDefaultHistory agreementHDH; ProjId projId; projId = (select agreementHDH where agreementHDH.AgreementHeaderHistory == 123).Project; }

The following statement is executed on SQL Server:

SELECT T1.PROJECT, T1.RECID FROM AGREEMENTHEADERDEFAULTHISTORY T1 WHERE ((PARTITION=?) AND (AGREEMENTHEADERHISTORY=?))

Select statement in X++ has no field list, but as it is field select Project field is selected in T-SQL. Very good so far.
RecId field is part of the primary index and is selected additionally, you can find more details on this in the previous post.
Note: if there are several records with the same AgreementHeaderHistory value, then all records will be fetched in SQL Server. As only one record is actually used in AX, it is better to add firstonly keyword.

Case 2. Table with a natural key as a primary index

Let's run field select on BankAccountStatement table:

static void selectFieldTest2(Args _args) { BankAccountStatement bankAccountStatement; CurrencyCode currencyCode; currencyCode = (select bankAccountStatement where bankAccountStatement.AccountId == "123").CurrencyCode; }

The following statement is executed on SQL Server:

SELECT T1.CURRENCYCODE, T1.ACCOUNTID, T1.ACCOUNTSTATEMENTDATE, T1.ACCOUNTSTATEMENTNUM, T1.PARTITION, 101090 FROM BANKACCOUNTSTATEMENT T1 WHERE (((PARTITION=?) AND (DATAAREAID=?)) AND (ACCOUNTID=?))

The same logic applies here, all fields of the primary index are additionally selected in T-SQL.

Conclusion
When select statement on a field is executed, only the field and primary index fields are fetched in SQL Server.
As only one record is actually used in AX, adding firstonly keyword to a field select statement can improve performance.