Index vs IndexHint

what is basic difference between index and indexhint in select statement.
While fetching data from the database we use select statement and for better performance in data fetching we use an index/indexhint.

Index: when we use index in select statement it implies that the declare index field should behave as an order by and it is optional for the database to use. Database can use its own preference.

Index hint: it force the DB to use that index for fetch the data whether it is right choice or not.
-----------------------------------------------------------------------------------------------------------------
index keyword in a select statement the kernel will translate this to a order by command and the database optimizer will chose the best index to actually use. When you chose to use the index hint keyword in your select statement, Ax will force the database to use the chosen index.
-------------------------------------------------------------------------------------------------------------------
What is the difference between index and index hint ?

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4
Index :
Using "Index": when you add the statement "index MyIndex", the Axapta kernel will add an "ORDER BY" with all the fields of the index.

Example: select * from InventTable index GroupItemIdx will generate the following SQL statement to the database:

SELECT A.ITEMGROUPID, A.ITEMID, A.ITEMNAME,.... FROM INVENTTABLE A ORDER BY A.ITEMGROUPID, A.ITEMID


The Index ItemGroupIdx of the InventTable exactly contains the two fields ItemGroupID and ItemId (in that order). Using "index", you still give the control of which index to use to the database optimizer. So, if the optimizer finds a better index to use, it will use it.

Index hint
Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4
Using "Index hint": when you add the statement "index hint MyIndex", the Axapta kernel will add a statement to instruct the database to use that index and no other one.

Example: select * from InventTable index hint GroupItemIdx will generate the following SQL statement to the database:

SELECT /*+ INDEX(A I_175GROUPITEMIDX) */ A.ITEMGROUPID, A.ITEMID, A.ITEMNAME,.... FROM INVENTTABLE A

Using "index hint", you take away the control of which index to use from the database optimizer. So, if there may be a better index, the database will not use it.

Conclusion:

Adding the "index" statement to an Axapta select, it does NOT mean that this index will be used by the database. What it DOES mean is that Axapta will send an "order by" to the database.

Adding the "index hint" statement to an Axapta select, it DOES mean that this index will be used by the database (and no other one)

=====================================================================
static void IndexHintExample(Args _args)
{
    CustTable custTable;

    custTable.disableCache(true);

    select generateonly forceLiterals MainContactWorker from custTable index hint HcmWorkerIdx
       where custTable.MainContactWorker == 0;

    info(CustTable.getSQLStatement());
}

Output:-SELECT T1.MAINCONTACTWORKER,T1.RECID FROM CUSTTABLE T1 WHERE ((DATAAREAID=N'ceu') AND (MAINCONTACTWORKER=0))
-------------------------------------------------------------------------------------------------------
static void IndexHintExample(Args _args)
{
    CustTable custTable;

    custTable.disableCache(true);

    select generateonly forceLiterals MainContactWorker from custTable index  HcmWorkerIdx
       where custTable.MainContactWorker == 0;

    info(CustTable.getSQLStatement());
}

Output:-SELECT T1.MAINCONTACTWORKER,T1.RECID FROM CUSTTABLE T1 WHERE ((DATAAREAID=N'ceu') AND (MAINCONTACTWORKER=0)) ORDER BY T1.DATAAREAID,T1.MAINCONTACTWORKER