Difference between InMemory and TempDB tables in D365 F&O

Difference between InMemory and TempDB tables in D365 F&O

There are two types of Temporary tables, TempDB tables, and InMemory tables, and the differences between them are:

InMemory tables	TempDB tables
1. Holds data temporarily in the client or server tier	1. Holds data temporarily in the database until 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 have foreign key columns.
4. TempDB tables can be instantiated from the client or server tier.
5. Can have indexes columns.
6. Can have methods, but cannot override.
7. Usable as a query
8. 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.

The 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. A controlled restart of the AOS.
3. A restart of the database system.
4. Closure of the AX32.exe client.
5. From Online Users form.

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 whichever 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.

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: