Index and Order By in Select Statements

Article
05/18/2015

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

Use the order by keyword in your select statements to order the data that's returned.

Use the index hint keywords to specify that a particular index should be used in the query and to sort the selected records as defined by the index. Indexes optimize the selection of records.

Combine the index hint keyword with an order by expression to select records in a specific order. If you want the sorted output in reverse order, use the reverse keyword.

If a table index has been disabled by setting the index's Enabled property to No, the select statement that references the index is still valid. However, the database can't use the index as a hint for how to sort the data, because the index doesn't exist in the database.

The following table is an overview of how to use the index hint and order by keywords in select statements.

To	Use
Select records where the order isn't significant.	select .. where ...
Select records where the order is significant.	select .. order by ... where ...
Select records and force a specific index to be used.	select .. index hint ... where ...
Select records where the order is significant and force a specific index to be used.	select .. index hint ... order by ... where ...

Example 1

To select the transactions from the salestable based on a range of customers and due dates, use the following code.

    SalesTable salesTable;
        select salesTable
        index hint CustIdx
        order by CustAccount
        where salesTable.CustAccount >= '3000'
              && salesTable.CustAccount <= '4000'
                        && salesTable.FixedDueDate >= 12\12\2004
                        && salesTable.FixedDueDate <= 05\05\2009;

Using Index Hints

To use index hints in queries you must first specify the use of hints on the server using the following procedure.

Open Start > Administrative Tools > Microsoft Dynamics AX Server Configuration and select the Database Tuning tab.
Select Allow INDEX hints in queries and click OK.
A message box prompting you to restart the AOS service appears. Click Yes to restart the AOS service. Index hints won't be enabled until the service is restarted.

Note

When an index hint in a select statement refers to a non-clustered index and the WHERE clause contains only the fields that are found in a clustered index on the same table, the clustered index is used instead of the index specified in the hint.

For example, if you run sp_helpindex InventTable in SQL Server Management Studio, you see that the InventTable has a clustered index on the DataAreaId and ItemId columns and a non-clustered index on the DataAreaId, ItemProductId, and ItemType columns.

Index name	Description	Key columns
I_175ITEMIDX	Clustered, unique, primary key located on PRIMARY	DATAAREAID, ITEMID
I_175PRODUCTIDX	Nonclustered located on PRIMARY	DATAAREAID, ITEMPRODUCTID, ITEMTYPE

In the following code the clustered index will be used instead of the non-clustered index specified in the index hint.

    static void IndexHint(Args _args)
    {
        InventTable inv;
        ;
        select * from inv index hint GroupItemIdx 
            where inv.ItemId == 'B-R14';
    }

Index and Order By in Select Statements

Example 1

Using Index Hints

See also

Additional resources