X++ Standards: select Statements

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

The basic rules for select statements are as follows:

• Specify what records you need by using the where statement to limit the returned rows. To sort the returned records, use the order by statement.

• Add an index hint on realistic data only if you discover performance problems without the hint.

• Use the index keyword if the order should be defined by the index definition.

Ordering of Data

Most select statements should be written without an index or an index hint, leaving the job of ordering the data to the optimizer in the database system. Whenever you use the index hint functionality, make a comment about why you are explicitly specifying it, thereby taking control away from the database management system (DBMS). Consider using the forceLiterals or forcePlaceholder statements as well or instead of select statements.

Use the order by statement when you want the data ordered, and you do not want to specify which index to use. Ordering of data can take time and should only be done if needed.

Use the index keyword when you want the data to be centrally ordered as specified in the index specification on the table. If the index is specified as Enabled: No, an index is not generated by the database system.

The use of the index statement can sometimes result in an index hint.

Using the firstOnly Qualifier

If you are going to use only the first record or if only one record can be found, use the firstOnly qualifier. This optimizes the select statement for only one record.

Do not use while select firstOnly.

The firstOnly qualifier and the field list are implicit in (select...).< field > statements, and are not explicitly needed. Use this kind of select statement wherever reasonable.

It is a best practice to use the firstOnly qualifier in find methods on tables.

Using select Statements Locally in Methods

If a select statement is local to a method, use a field list to increase performance. If you use a select or a while select statement and the size of the fields that are used total less than 50 percent of the total record size, a warning appears if you do not use a field list.

If a select statement is local to a method and the firstOnly qualifier is not used, you must use the next statement on the result set.

Layout Examples

Here are two (contrived) examples of how to find ledger transactions in account number, transaction date order.

The following code shows how to find the last week's (that is, a few days) transactions on all of the (many) Profit and Loss accounts.

select firstOnly ledgerTrans

     index hint DateIdx

    order by accountNum, transDate

    where ledgerTrans.accountNum >= '40000'    

        && ledgerTrans.accountNum <= '99999'    

        && ledgerTrans.transDate >= 26\04\1999

        && ledgerTrans.transDate <= 02\05\1999;

The following code shows how to find transactions for the entire year (many dates) on (the few) liquid assets accounts.

while select ledgerTrans

    order by accountNum, transDate

    where ledgerTrans.accountNum >= '11100'    

        && ledgerTrans.accountNum <= '11190'    

        && ledgerTrans.transDate >= 01\07\1999

        && ledgerTrans.transDate <= 30\06\2000

{

    // Do whatever is needed.

    print ledgerTrans.amountMST;

}

Following are best practices from the previous examples:

• The index, order by, and where statements are indented once relative to the select or while select statements.

• The where expression is structured in a column.

• The Boolean operators (&&) are at the beginning of the line (and in columns).

• The while select block has braces even though it contains only one statement.

• The braces are at the same column position as the while block.

• The uppercase- and lowercase-name standards are adhered to.

See also

Select Statements

Queries Best Practices

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.