TableType Property - Tables in D365

Rumman Ansari   Software Engineer   2023-05-15   1267 Share
☰ Table of Contents

Table of Content:

TableType Property - Tables in D365

This unit explains temporary tables, the different types of temporary tables, and when they should be used.

Temporary tables allow you to efficiently create and clean up temporary data. The two types of temporary tables are:

  • InMemory
  • TempDB
 TableType Property - Tables in D365

The table type can be determined on the table's TableType property.

TableType Property

The following table describes the TableType property.

Value

Description

Regular

The default value. These are permanent tables.

Temporary InMemory

A temporary table that exists as an indexed sequential access method (ISAM) file. The ISAM file can exist on either the client tier of the Application Object Server (AOS) tier. The underlying Microsoft SQL Server has no connection to the ISAM file.

The data is stored in memory until it reaches 128 KB, and then the dataset is written to a disk file on the server tier. InMemory tables are instantiated when the first record is inserted. The table exists and memory is only allocated to the table while a record buffer exists.

The system does allow you join an InMemory table in the X++ SQL syntax. However, joins and other set operations with InMemory tables are usually inefficient.

For more information, see Temporary InMemory Tables.

An InMemory table is the same thing as what was previously called a temporary table in Microsoft Dynamics AX 2009.

An InMemory table might be used when you need to store and retrieve data without writing data to the database. This is like a container, but InMemory tables allow you to use indexes to speed up data retrieval. If you are only using a few records, you should use a container, not an InMemory table. You can use X++ SQL syntax to join an InMemory table, however, joins and SQL operations are usually inefficient.

Temporary TempDB

A temporary table that resides in the TempDB database of the underlying SQL Server. The nonstandard format of a TempDB table causes the table to be dropped when it is no longer in use by the current method.

Joins and other set operations on TempDB tables can be efficient.

For more information, see Temporary TempDB Tables.

TempDB tables use the TempDB database of the SQL Server. This type of table causes the data to be removed when it is no longer used by the current method or when the system is restarted. Regular tables are automatically turned into TempDB tables by disabling the configuration key for the table, which allows references to the disabled table in the system to continue to compile and run. Additionally, TempDB tables are commonly used on reports to manipulate data.

The capabilities of TempDB tables include the following:

  • Joining to regular tables.
  • Using foreign keys.
  • Being per company or global.
  • Having indexes.
  • Having methods but an inability to override the methods.
  • Instantiating from the client or server tier.
  • Being used as a query.
  • Having no requirements for a configuration key.

TempDB tables also have the following limitations:

  • Inability to manage date-effective data.
  • They do not contain delete actions.
  • Record Level Security does not apply.
  • You cannot use them in views.

Comparison between Regular, InMemory and TempDB TableType Property in d365

Here's a comparison of the Regular, InMemory, and TempDB table types in Dynamics 365 in a table format:

Property/Feature Regular Table InMemory Table TempDB Table
Storage Location Application Database Memory System Database
Persistence Data is persisted to disk Data is stored in memory and lost on restart Data is lost when the table is no longer needed
Use Cases Permanent storage of application data Fast processing of temporary data Fast processing of temporary data
Scalability Limited by disk performance and database server capacity Limited by memory capacity of server Limited by disk performance and database server capacity
Query Performance Can be optimized with indexes and other database tuning techniques Fast due to in-memory storage, but cannot be optimized with indexes or other database tuning techniques Can be optimized with indexes and other database tuning techniques
Security Supports standard database security features Supports standard database security features Supports standard database security features
Transaction Handling Supports standard database transactions Does not support transactions Supports standard database transactions
Data Size Limited by disk capacity of server Limited by memory capacity of server Limited by disk capacity of server

Differences between Regular, InMemory and TempDB TableType Property in d365

Here's a table format response that highlights some of the key differences between Regular, InMemory, and TempDB table types in Dynamics 365:

Property/Feature Regular Table InMemory Table TempDB Table
Storage Location Application Database Memory System Database
Persistence Data is persisted to disk Data is stored in memory and lost on restart Data is lost when the table is no longer needed
Use Cases Permanent storage of application data Fast processing of temporary data Fast processing of temporary data
Indexing Supports standard database indexing Does not support indexes Supports standard database indexing
Transaction Handling Supports standard database transactions Does not support transactions Supports standard database transactions
Data Size Limited by disk capacity of server Limited by memory capacity of server Limited by disk capacity of server
Performance Generally slower than in-memory tables Generally faster than regular tables Generally faster than regular tables
Scalability Limited by disk performance and database server capacity Limited by memory capacity of server Limited by disk performance and database server capacity
Security Supports standard database security features Supports standard database security features Supports standard database security features
Availability Always available Always available Available only during specific process or operation

Note that this is not an exhaustive list and there may be other differences between these table types depending on the specific needs of your Dynamics 365 implementation.

‹ Previous -   Table Properties in D365 F&O
Next › -   Table Relations in D365