Temp Table pros and cons - Dynamcis AX

Most of the time we require Temp Tables in our solution. This post is about temp table's capabilities, limitations and life-cycle.

When we use temp tables, axapta kernel creates a table in SQL temp database with a different name.

Capabilities:

• Can be joined with normal tables in X++
• Can save Data Per Company
• Can be used in Enterprise Portal development
• Can have foreign keys from other tables
• Can have Indexes

Limitations:

• Cannot be a Valid Time State Table
• Cannot have Delete Action
• No Record Level Security (RLS)
• No View support (cannot be used in views)

Life-Cycle: When is temp table dropped from TempDB?

• Table buffer goes out of scope
• Restart of AOS service
• Restart of SQL service
• Closure of AX32.exe

Hope it helps !!!

System fields of tables in Dynamics AX

When we create table in Dynamics AX, we create fields in that table. Those fields are then created as columns in the SQL database. These are called Custom Fields.

The AX framework manages some fields by itself, these are called System Fields. Some of the system fields are automatically generated (with a table property value set to Always in below table) and some fields are created on the choice of the developer.

To create the desired system field on your table you need to set table's property according to the following table:

System field	Table property
RecId	Always
RecVersion	Always
DataAreaId	SaveDataPerCompany = Yes
CreatedBy	CreatedBy = Yes
CreatedDate	CreatedDate = Yes
CreatedTime	CreatedTime = Yes
CreateTransactionId	CreateTransactionId = Yes
ModifiedBy	ModifiedBy = Yes
ModifiedDate	ModifiedDate = Yes
ModifiedTime	ModifiedTime = Yes
ModifiedTransactionId	ModifiedTransactionId = Yes

Hope it helps !!!