Index

Index :

An index is a table-specific database structure that speeds the retrieval of rows from a table. Indexes are used to improve the performance of data retrieval and occasionally to ensure the existence of unique records.

There are two types of indexes: unique and non-unique. 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.

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. 

If there are indexes on a table but none of them are unique, the runtime estimates the average key length of the existing indexes, chooses the index with the smallest key length and appends the RecId column to create a unique index.

Create Index :

AOT >> Locate Table >> RC Indexesnode , New Index. RC NewField. ( In Property – Select DataField).

Order – for sorting, Allow Duplicates – No. Enable – No , when you disabled index it is deleted from the database.

Table Keys :

Property	Description
PrimaryIndex	The drop-down list contains the surrogate key plus every index on the table that has its AlternateKey property set to Yes.
CreateRecIdIndex	This property controls whether the system creates a unique index on the RecId field. The default value is Yes. This is the basis of the surrogate key. No other field is added to this index, not even DataAreaId.
ReplacementKey	The drop-down list contains every index that has its AlternateKey property set to Yes. You might change the default blank value to an index whose field values within each record provide a name or other moniker that is meaningful to people. If a ReplacementKey is chosen, its fields can appear on forms to helpfully identify each record. The ReplacementKey should be a set of fields that represent the natural key.
ClusterIndex	The ClusterIndex value is given to the underlying Microsoft SQL Server database system as a performance tuning choice. This choice generally controls the physical sequence in which the records are stored in the underlying database.

he following AOT image highlights the table properties that are related to keys.

Alternate Key

A table can have several alternate keys. Any one alternate key can switch to being the primary key, if the alternate key is comprised of only one field.

Property	Description
AllowDuplicates	No means that the combined fields of the index must together make a value in each record which no other record has.
AlternateKey	Yes means that other tables can create foreign key relations that reference this key, as an alternative to referencing the primary key. Indexes with two or more fields cannot have their AlternateKey property value set to Yes.
ValidTimeStateKey	A key that is marked as a valid time state key is not a candidate key for child tables to reference in their foreign key relations. Instead, this key is meant for managing date effective data in its own table. The default is No. This field can be Yes only if the ValidTimeStateFieldType property is Yes on the table. Yes means this key contains the ValidFrom and ValidTo fields. The ValidTimeStateKey property cannot be set to Yes when the AlternateKey property is set to No.

Other Terminology for Keys :

Term	Description
foreign key	In Microsoft Dynamics AX, an AOT node under MyTable > Relations represents a foreign key. For more information, see the previous Relations section in this topic.
natural key	A key whose value has meaning to people. Most replacement keys are natural keys.
surrogate key	A key whose value has no meaning to people. A large number generated by the system, such as RecId, could be a surrogate key.
unique key	A broad term that applies to primary keys and to alternate keys. It does not apply to foreign keys. This term emphasizes that all values for a given key must be unique within one table. All fields in a unique key must be not-nullable.

Full Text Index :

A full text index contains location information about each significant word in a string field of a table. Some queries can use this information to run more efficiently and complete much sooner. These are queries that search for words that are embedded in the middle of string fields.

Surrogate Key

The Surrogate (1) definition relates to a data model rather than a storage model and is used throughout this article. See Date (1998).

An important distinction between a surrogate and a primary key depends on whether the database is a current database or a temporal database. Since a current database stores only currently valid data, there is a one-to-one correspondence between a surrogate in the modeled world and the primary key of the database. In this case the surrogate may be used as a primary key, resulting in the term surrogate key. In a temporal database, however, there is a many-to-one relationship between primary keys and the surrogate. Since there may be several objects in the database corresponding to a single surrogate, we cannot use the surrogate as a primary key; another attribute is required, in addition to the surrogate, to uniquely identify each object.

a surrogate should have the following characteristics:

the value is unique system-wide, hence never reused

the value is system generated

the value is not manipulable by the user or application

the value contains no semantic meaning

the value is not visible to the user or application

the value is not composed of several values from different domains.