TableType Property - Tables in D365

 

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

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.