index: An index is a table-specific database structure that speeds the retrieval of
rows from a table.
Uses: Indexes are used to improve the performance of data retrieval and occasionally to ensure the existence of unique records.
location in AOT: AOT>Data Dictionary>Tables
* Once created, indexes are managed automatically by the DBMS every time a record is inserted, updated, or deleted.
* An index is defined by one or more fields.
* When selecting table fields for an index consider the following:
Fields that are often searched by a range.
Fields that frequently participate in joins.
Fields that are frequently used to order or group a result set.
Types of index: unique or primary
Non-unique or cluster
unique or primary:Whether an index is unique is defined by the index's AllowDuplicates property. When this property is set to No, a unique index is created.
The database prevents you from inserting records with duplicate key values by rejecting the insert
Non-unique:Setting the index's AllowDuplicates property to Yes creates a non-unique index.
These indexes allow you to enter duplicate values for the indexed fields and are used for performance reasons.
Note: A field of data type memo or container cannot be used in an index.
system index:Microsoft Dynamics AX requires a unique index on each table so if there are no indexes on a table or all the indexes are disabled, a system index is automatically created.
The system index is created on the RecId and DataAreaId fields if the DataAreaId field exists. Otherwise the system index is created on the RecId field. You can see system indexes in the database but they aren't visible in the AOT.
How to create an index:
http://msdn.microsoft.com/EN-US/library/aa607289.aspx
Table Keys:
http://msdn.microsoft.com/EN-US/library/hh812105.aspx
Difference between primary and cluster index:
Primary index: this index used to organize both the data store and other indexes for more efficient updating and faster access.
cluster index: this index are created for performance reasons. they provide a quick way of retriving data, instead of performing a full-table search of all the records in the table.
Difference between Index and Index Hint :
Index: when you add the statement "index MyIndex", the axapta kernel will add an "ORDER BY" with all the fields of the index.
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.
Uses: Indexes are used to improve the performance of data retrieval and occasionally to ensure the existence of unique records.
location in AOT: AOT>Data Dictionary>Tables
* Once created, indexes are managed automatically by the DBMS every time a record is inserted, updated, or deleted.
* An index is defined by one or more fields.
* When selecting table fields for an index consider the following:
Types of index: unique or primary
Non-unique or cluster
unique or primary:Whether an index is unique is defined by the index's AllowDuplicates property. When this property is set to No, a unique index is created.
The database prevents you from inserting records with duplicate key values by rejecting the insert
Non-unique:Setting the index's AllowDuplicates property to Yes creates a non-unique index.
These indexes allow you to enter duplicate values for the indexed fields and are used for performance reasons.
Note: A field of data type memo or container cannot be used in an index.
system index:Microsoft Dynamics AX requires a unique index on each table so if there are no indexes on a table or all the indexes are disabled, a system index is automatically created.
The system index is created on the RecId and DataAreaId fields if the DataAreaId field exists. Otherwise the system index is created on the RecId field. You can see system indexes in the database but they aren't visible in the AOT.
How to create an index:
http://msdn.microsoft.com/EN-US/library/aa607289.aspx
Table Keys:
http://msdn.microsoft.com/EN-US/library/hh812105.aspx
Difference between primary and cluster index:
Primary index: this index used to organize both the data store and other indexes for more efficient updating and faster access.
cluster index: this index are created for performance reasons. they provide a quick way of retriving data, instead of performing a full-table search of all the records in the table.
Difference between Index and Index Hint :
Index: when you add the statement "index MyIndex", the axapta kernel will add an "ORDER BY" with all the fields of the index.
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.
No comments:
Post a Comment