Difference between InMemory and TempDB tables in AX 2012

Basically in AX2012 there are two types of Temporary tables, (i)TempDB tables and (ii)InMemory Tables.

Basic differences between them are:

InMemory tables	TempDB tables
1. Holds data temporarily in client or server tier	1. Holds data temporarily in database till the scope is valid
2. These tables can't be stored in Database	2. These tables are stored in the database
3. Can't apply security	3. Can apply security
4. We cannot use InMemory table buffers	4. TempDB table buffer can be used in coding

TempDB tables

We call them TempDB tables because their TableType property value is TempDB. This value comes from the TableType::TempDB enum value. The TableType property value can be set at AOT > Data Dictionary > Tables > MyTempDBTable > Properties > TableType.
All types of temporary tables are automatically dropped by the system when the table variable in X++ goes out of scope. A TempDB table is NOT dropped when you set its record buffer variable to null.

Capabilities of TempDB Tables:

1. Can be joined with other AX tables
2. Can be either per company or global.
3. Can be used from Enterprise Portal by using .NET Business Connector.
4. Can have foreign key columns.
5. TempDB tables can be instantiated from the client or server tier.
6. Can have indexes columns.
7. Can have methods, but cannot override.
8. Usable as a query
9. Transaction support.
10. No configuration key is required.

Limitations of TempDB Tables:

1. Cannot be a valid time state table.
2. Cannot have any delete actions.
3. No Record Level Security (RLS).
4. Cannot use the Table browser form.
5. Cannot be in a table collection.
6. No view support.

Lifetime of TempDB Tables:
A TempDB table is instantiated in the underlying database management system when the first SQL operation is sent to the database system from the AOS. (select, insert, update, or delete)

The situations that cause a TempDB table to be dropped are:

1. Variable goes out of scope.
2. Controlled restart of the AOS.
3. Restart of the database system.
4. Closure of the AX32.exe client.
5. From Online Users form.

Example to Illustrate difference between TempDB tables and InMemoryTables:

//This example is done in AX 2012 R3 CU8 6.3.1000.309
static void Aj_TempdbTableUsage(Args _args)
{
    //Aj_MyTempdbTable xrecMyTempdb;
    Aj_MyInMemoryTable xrecMyTempdb; //Just to illustrate te differene between InMemory and TempDB tables
    CustTable xrecCustTable;
    TableType tableTypeEnum;
    str stringWork;
    Global::info(strFmt("Start of main.")); //<<%1>>", DateTimeUtil::utcNow()));
    xrecMyTempdb.AccountNum = "DE-001";
    xrecMyTempdb.doInsert();
    xrecMyTempdb.AccountNum = "US-001";
    xrecMyTempdb.doInsert();
    tableTypeEnum = xrecMyTempdb.getTableType();
    stringWork = "Aj_MyTempdbTable.TableType is: " + enum2Str(tableTypeEnum);
    info(stringWork);
    while select *
            from xrecCustTable
                JOIN xrecMyTempdb //Illustrating the join capabilities
            where
                xrecMyTempdb.AccountNum == xrecCustTable.AccountNum
    {
        stringWork = xrecCustTable.AccountNum
                + " , "
                + int2Str(xrecCustTable.MandatoryCreditLimit);
        info(stringWork);
    }
}
/*** Infolog outputMessage (05:21:28 pm)
Start of main.
MyTempdb.TableType is: TempDB
DE-001 , 0
US-001 , 1
***/

Output:

 
TempDB Tables for Disabled Tables
You can disable a regular persisted database table by disabling the configuration key that controls the table. Disabling the key causes the system to automatically create a TempDB style of temporary table that matches the fields and schema of the database table. This temporary table exists in the underlying SQL Server database and is managed by the Application Object Server (AOS).
The purpose of automatically creating this TempDB table is to enable AOT objects that reference the disabled table to continue to compile and run. You can read and write to this TempDB table even though the configuration key is disabled.
Note: All table buffer variables inherit the methods of the xRecord class. One such method is setTmp, which creates an InMemory temporary table that has the same schema as the regular table. However, the setTmp method cannot create an InMemory table from a TempDB table. You can call the method isTempDb to determine whether the setTmp method is available.


InMemory tables

The second type of temporary table is the InMemory table. We call them InMemory tables because their TableType property value is InMemory. This value comes from the TableType::InMemory enum value. The TableType property value can be seen at AOT > Data Dictionary > Tables > MyInMemoryTable > Properties > TableType.

Note:
In Microsoft Dynamics AX 2009 and earlier versions, InMemory tables were called temporary tables. Now there are two kinds of temporary tables, namely InMemory tables and TempDB tables. To avoid confusion we do not use the phrase temporary tables to refer to just InMemory tables or to just TempDB tables.

Tier: InMemory tables are instantiated in the active memory of which ever tier the process is running on, either the client or the server tier. InMemory tables are never represented in the database management system.
An InMemory table is held in memory until its size reaches 128 KB. The dataset is then written to a disk file on the server tier. The disk file for an InMemory table has the naming convention $tmp<nnnnnnnn>.$$$.

Scope: An InMemory table is instantiated when the first record is inserted. The instantiated InMemory table continues to exist only while a record buffer variable that references the table exists. The memory or disk space for the InMemory table is de-allocated as soon as the record buffer goes out of scope.

Indexes: can be defined on an InMemory table just as you would a persisted table. If an InMemory table is created by copying a persisted table, the indexes are also copied to the InMemory table. Indexes are useful for quickly retrieving data from InMemory tables, especially if the InMemory table data is in a disk file.

A Simple example:  

static void Aj_MyInMemoryTableUsage1()
{
    Aj_MyInMemoryTable custTmpLedger;
    ;
    custTmpLedger.Name = 'customer tmp 1';
    custTmpLedger.AccountNUm = "C0000001";
    custTmpLedger.insert();
    while select custTmpLedger
    {
        info(strFmt("Name: %1, AcccountNUm: %2", custTmpLedger.Name, custTmpLedger.AccountNUm));
    }
    custTmpLedger = null;
}

Output:
 

 
 
 
 
 
 
 
 
 
 
 

 
More details on InMemory Tables Vs. Containers to follow.