Index and Order By in Select Statements

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 ...

To select the transactions from last week from the set of all ledger transactions, use the following code.

select LedgerTrans
    index hint DateIdx
    order by AccountNumber, Date
    where LedgerTrans.AccountNumber >= '1000'
          && LedgerTrans.AccountNumber <= '4000'
                    && LedgerTrans.Date >= 28\04\97
                    && LedgerTrans.Date <= 05\05\97;

To select the transactions for the entire fiscal year, use the following code.

select LedgerTrans
    index hint ACDate
    order by AccountNumber, Date
    where LedgerTrans.AccountNumber >= '7000'
          && LedgerTrans.AccountNumber <= '7008'
          && LedgerTrans.Date >= 01\07\96
          && LedgerTrans.Date <= 05\05\97;

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

  1. Open Start > Administrative Tools > Microsoft Dynamics AX Server Configuration Utility and select the Database Tuning tab.

  2. Select Allow INDEX hints in queries and click OK.

  3. 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.

NoteNote

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, ItemGroupId, and ItemId columns.

Index name

Description

Key columns

I_175ITEMIDX

Clustered, unique, primary key located on PRIMARY

DATAAREAID, ITEMID

I_175GROUPITEMIDX

Nonclustered located on PRIMARY

DATAAREAID, ITEMGROUPID, ITEMID

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';
}

Community Additions

ADD
Show: