Temporary tables – InMemory vs. TempDb
The choice of whether to use a TempDB or InMemory temporary table depends on the scenario where it is used. Before using these tables the following considerations must be followed for best practice prospective.
- Usually, TempDB temporary tables are faster than in InMemory ones.
- Temporary tables are maintained in the database and support joins with regular tables.
- InMemory temporary tables are instantiated in the active memory of the tier the process is running on. The process can run on the client tier or the server tier. The objects are held in memory until the size reaches 128 KB. The dataset is then written to a disk file on the server tier.
- You can use InMemory temporary tables when the amount of data is small and Microsoft SQL Server round trips should be avoided.
- Both InMemory and TempDb tables can be used as data sources on forms.
Note: An InMemory table cannot be joined in an X++ SQL statement with Regular/TempDB tables. It must be the outer table in a join. It is better to use TempDB temporary tables, because SQL operations are faster due to being maintained in the database. In Figure 3, you can see the TableType property for a table, where you can set the type as Regular, InMemory, or TempDB.