Difference between InMemory and TempDB tables in AX 2012

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:

• Can be joined with other AX tables
• Can be either per company or global.
• Can be used from Enterprise Portal by using .NET Business Connector.
• Can have foreign key columns.(A TempDB table can have a foreign key column that references the primary key of another table. However, no table can have a foreign key column that references the primary key of a TempDB table.)
• TempDB tables can be instantiated from the client or server tier.
• Can have indexes columns.(A TempDB table can have indexes defined for it in the AOT.)
• You can add methods to a TempDB table. However, you cannot override any methods that come with a new TempDB table.
• Usable as a query(A query under AOT > Queries can reference a TempDB table as a data source.)
• Transaction support. (The underlying database management system does provide transaction support for each instance of a TempDB table, just as it does for any regular table. Each instance is a separate table that is unrelated to the other instances, and each instance has a brief lifetime.)
• No configuration key is required.
• Limitations of TempDB Tables:
• Cannot be a valid time state table.
• Cannot have any delete actions.
• No Record Level Security (RLS).
• Cannot use the Table browser form.
• Cannot be in a table collection.
• 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: 

• Variable goes out of scope.
• Controlled restart of the AOS.
• Restart of the database system.
• Closure of the AX32.exe client.
• 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 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.

InMemory Tables vs. Containers

Microsoft Dynamics AX supports a special data type called a container. This data type can be used just as you would use an InMemory table. For more information, see Containers.

Data in containers are stored and retrieved sequentially, but an InMemory table enables you to define indexes to speed up data retrieval. An index is of no benefit if you are working with only a few records. In such cases a container might involve less overhead and perform faster than an InMemory table.

Another important difference between InMemory tables and containers is how they are used in method calls. When you pass an InMemory table into a method call, it is passed by reference. Containers are passed by value. When a variable is passed by reference, only a pointer to the object is passed into the method. When a variable is passed by value, a new copy of the variable is passed into the method. If the computer has a limited amount of memory, it might start swapping memory to disk, slowing down application execution. When you pass a variable into a method, an InMemory table may provide better performance than a container.

Conclusion : If you have just a few kilobytes of data, InMemory tables holds them in RAM, which is extremely fast. But if you have more data, it gets written on disk, not to waste RAM. Disks are much slower than RAM, of course. But you still can have data directly on client or AOS, without having to go to database.

But TempDB can use the whole power of SQL Server, which may make a big difference if you're running queries on large sets of temporary data.

Source :- https://msdn.microsoft.com/en-us/library/gg845661.aspx