Temporary tables in AX 2012

1.       In a developer's point of view, temporary tables store data in the same way as normal physical tables, except that the data is automatically dropped when no longer required.

2.       Prior to Dynamics AX 2012 versions, only one type of temporary table was available. In AX 2012, however, the Temporary property on tables was replaced with a new property: TableType, which has three possible values:

·         Regular - a standard physical table

·         InMemory - the type of temporary table which existed in the previous versions of Dynamics Ax. Such tables are held in memory and written to a local disk file once they grow beyond a certain point

·         TempDB - a new option in Ax 2012. They are "physical" temporary tables held in the SQL Server database.

3.       The new TempDB tables operate in a similar manner to InMemory tables but support more features from standard physical tables:

·         More powerful joins with physical tables are possible, and are properly supported by the database

·         Can be per-company or global

·         Support for normal tts transactions

4.       You can convert a normal table to a temporary table in code. After doing this, You can now add, modify or delete data from the table without affecting the real contents stored in the database. For example, you can make a regular table as a temporary table as shown below.

InventTable    inventTable;

;

inventTable.setTmp();

5.       Indexes can be created on temporary tables as we doing on Regular tables. When a temporary copy of a normal table is used with .setTmp(), then the existing indexes will also be created on the temporary version.

6.       Indexes will show more impact on temporary table performance. For temporary tables with a lot of records you will experience major performance limitations when searching on non-indexed fields.