Dynamics AX 2012, some facts about Tables

In Microsoft Dynamics AX 2012 a new type of temporary table is added, ie now you have three types of Table,
Regular Tables: AOT tables used for storing data perminently in database
InMemory Tables: This type of temporary table is hosted in the client, the data will wipe out as soon as instance of the form or report (one who loaded the data in InMemory tables) is closed.
TempDB Tables: It is the new type of temporary table that is hosted in the Microsoft SQL Server database.Now the temporary tables can be joined on the database tier with regular tables. This was the joining problem we have in InMemory Tables.

Configuration Key Impact on the Tables.

In Microsoft Dynamics AX 2012, when the configuration key for a table that is listed in the AOT is disabled, the table is not dropped from SQL Server. In the earlier version of Microsoft Dynamics AX when changing the configuration key to disable a table, the table is dropped from the SQL database, deleting all data in the table.
In AX 2012 we can disable the table, but the data will not be lost, as a result Sql Server cubes or Enter application can read the data because they bypass the AOS.

Relation on the Tables.

In Microsoft Dynamics AX 2012, you should always have relations between the two tables on RecId field so the primary key for the new tables created in AX 2012 is always be Surrogate key (RecId). for Example you are creating any new table in AX 2012 so you always have the RecId field as the primary key and if you want to make relattion with any child table then you will always make the relation on RecId field and move it as a Foreign Key in child table. This is the major improvement, as the tables are now completely normalized, no redendency of data, upgrade will be easy by that to.

If you would like to know more about Surrogate key, see this link.

In order to create new Foreign key relationship (New Type added in AX 2012), Right-click on the newly created and configured Relation and choose New > New Foreign Key. You should see something similar to that

Note that the creation of a surrogate foreign key relationship automatically adds a field to the foreign key table for you!

Full Text Indexes on Tables

Microsoft Dynamics AX 2012 provides full-text functionality that enables Microsoft Dynamics AX to search business data over a large volume of text data or documents. You can create a full-text index on tables of type Main and Group.

You must configure the SQL Server full-text search functionality before you use it in the Microsoft Dynamics AX application

Same as regular SQL Server indexes, full-text indexes can be automatically updated at the same time that the data is changed in the associated tables. see related links on how to create and Use the Full text Indexes.