Dynamics Axapta 2009
Index :
An index in a database is essentially a quick lookup table which makes it faster to find records (rows) in our tables.
The indexes in the Microsoft Dynamics AX 2009 table definition are the physical indexes that exist on the tables in the database.
There are two types of indexes:
Unique or Primary
Non-Unique
If a unique index is created based on a column (or a set of columns), Microsoft Dynamics AX 2009 makes sure that no duplicate keys occur in that column (or set of columns).
A primary index is an index used to organize both the data store and other indexes for more efficient updating and faster access.
Non-unique, or cluster indexes, are created for performance reasons. They provide a quick way of retrieving data, instead of performing a full-table search of all the records in the table.
A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. For example, if you want to make sure that the values in the NationalIDNumber column in the HumanResources.Employee table are unique, when the primary key is EmployeeID, create a UNIQUE constraint on the NationalIDNumbercolumn. If the user tries to enter the same value in that column for more than one employee, an error message is displayed and the duplicate value is not entered.
What is the difference between index and index hint ?
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
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).