InMemory vs. TempDB Temporary Tables in Dynamics 365 Finance & Operations
Question 4 — The two temporary table types, how they differ, and when to use each.
Interview Question
Model Answer (Short)
Both are temporary tables that hold data only for the duration of a scope, but they live in different places. An InMemory table keeps its data in memory (and in a temporary file on disk once it grows), so it is best for small datasets — typically under about a thousand records. A TempDB table is created in the SQL Server tempdb database, so it behaves like a real SQL table, supports joins with regular/TempDB tables, and performs far better for large datasets. Rule of thumb: small data → InMemory; large data → TempDB.
Detailed Explanation
InMemory table
- Data is stored in memory; it spills to a temporary file on the client/server when large.
- Ideal when the expected number of records is less than ~1000.
- Avoids round trips between AOS and the database for small, repeatedly-referenced data.
- Cannot be joined to a regular or TempDB table in an X++ SQL statement.
TempDB table
- Physically created in the SQL Server tempdb database.
- Recommended when data volume is 1000 records or more.
- Supports joins with regular and other TempDB tables, indexes, and set-based operations.
- Maintains proper T-SQL behaviour in the database, so it scales far better for large volumes.
Prerequisites (Rule 5)
- Visual Studio with the Dynamics 365 developer tools.
- A custom model / package for your objects.
- A table whose
TableTypeproperty is set to InMemory or TempDB. - Alternatively, use
setTmp()at runtime to make a regular table temporary.
Code Example — Using an InMemory table
Populating and reading a small InMemory temporary table:
AbcTmpBalance tmpBalance; // TableType = InMemory
// Insert a few records
tmpBalance.AccountNum = '1001';
tmpBalance.Balance = 5000;
tmpBalance.insert();
tmpBalance.AccountNum = '1002';
tmpBalance.Balance = 7500;
tmpBalance.insert();
// Read them back
while select tmpBalance
{
info(strFmt("%1 : %2", tmpBalance.AccountNum, tmpBalance.Balance));
}Making a regular table temporary at runtime
CustTable custTableTmp;
// Turn a non-temporary buffer into a temporary one
custTableTmp.setTmp();
custTableTmp.AccountNum = 'C0001';
custTableTmp.insert(); // goes to the temporary buffer, not the real tableTempDB table joined with a regular table
AbcTmpSalesSummary tmpSummary; // TableType = TempDB
SalesTable salesTable;
while select tmpSummary
join salesTable
where salesTable.SalesId == tmpSummary.SalesId
{
// TempDB tables CAN be joined with regular tables
}InMemory vs. TempDB
| Aspect | InMemory | TempDB |
|---|---|---|
| Storage location | Memory (spills to a temp file when large) | SQL Server tempdb database |
| Best data volume | Small (< ~1000 records) | Large (≥ 1000 records) |
| Join with regular / TempDB tables | Not supported in X++ SQL | Fully supported |
| Indexes | Limited benefit | Real SQL indexes usable |
| AOS ↔ DB round trips | Minimised for small data | Behaves like a database table |
Points the interviewer wants to hear
- InMemory lives in memory; TempDB lives in the SQL tempdb database.
- Use InMemory for small datasets, TempDB for large ones.
- InMemory tables cannot be joined with regular/TempDB tables in X++ SQL.
- TempDB supports joins, indexes and set-based operations.
- Prefer
setTmp()over duplicating a table to keep the two versions in sync.
Likely Follow-up Questions
- Why can't you join an InMemory table with a regular table?
- What does the
setTmp()method do, and why is it preferred over cloning a table? - Where does an InMemory table's data go once it exceeds the memory threshold?
Key Takeaway
Pick InMemory for small, frequently-referenced datasets to avoid database round trips, and TempDB for large volumes where you need joins, indexes and set-based performance. Matching the temporary table type to the data volume is the key decision.