Temporary Tables In Dynamics AX

In Microsoft Dynamics AX, a table typically maps to a corresponding table in the database. Temporary tables enable to you define table objects that are not persisted to the database. Define a table as a temporary table by doing one of the following:

• Set the table's Temporary property to Yes at design time.

  –or–

• Call the setTmp method in X++ code.

A temporary table is held in memory until its size reaches 128 KB. The dataset is then written to a disk file. The disk file for a temporary table has the naming convention $tmp<nnnnnnnn>.$$$.

A temporary table is located in the Application Object Tree (AOT) under the Data Dictionary\Tables node, just like a persisted table. If a table is defined as temporary, you can use it just as you would a static table. All X++ DML statements can be run against a temporary table.

Note: It is a best practice to infix temporary table names with Tmp. This improves readability in code.

Scope      A temporary table exists only while a record buffer variable that references the table exists. No memory is allocated to the temporary table until the first record is inserted. At that point, memory is allocated. Disk space is allocated, if it is needed. As soon as the record buffer goes out of scope, the memory is de-allocated and the disk file is deleted.

A temporary table resides on the tier where the first record is inserted. If a record is inserted on the server tier, memory for the temporary table is allocated on the server tier. If the temporary table exceeds 128 KB, a disk file is created on the server.

Adding data to temp table

To add data to a temporary table, you must define the record buffer and call the insert method. The following is a code example that uses the TmpCustLedger table.

static void TableTmpInsertRecord(Args _args)
{
    TmpCustLedger custTmpLedger;
    ;
    custTmpLedger.AccountNum = '1000';
    custTmpLedger.Name = 'NameValue';
    custTmpLedger.Balance01 = 2345000;
    custTmpLedger.insert();
}

To free the memory and delete the file for the temporary table, set the record buffer variable to null, as follows.

custTmpLedger = null;

To populate a temporary table with data from a persisted table, use the setTmp method. The following code example copies all customers in Toronto to the temporary table.

static void CopyPersistedTableToTemp(Args _args)
{
    CustTable custTable;
    CustTable custTmpLedger;
    ;
    custTmpLedger.setTmp();
    custTable.recordLevelSecurity(true);

    while select custTable where custTable.City == 'Toronto'
    {
        custTmpLedger.data(custTable.data());
        custTmpLedger.doInsert();
     }
}

Indexes

Indexes can be defined on a temporary table just as you would a persisted table. If a temporary table is created by copying a persisted table, the indexes are also copied to the temporary table. Indexes are very useful for retrieving data in temporary tables especially if the temporary table data is in a disk file.

Temporary table vs. Container 

Microsoft Dynamics AX supports a special data type called a container. This data type can be used just as you would use a temporary table.

• Data in containers are stored and retrieved sequentially, but a temporary table enables you to define indexes to speed up data retrieval.
  
• Containers provide slower data access if you are working with many records. However, if you are working with only a few records, use a container.
  
• Another important difference between temporary tables and containers is how they are used in method calls. When you pass a temporary table into a method call, it is passed by reference. Containers are passed by value. When a variable is passed by reference, only a pointer to the object is passed into the method. When a variable is passed by value, a new copy of the variable is passed into the method. If the computer has a limited amount of memory, it might start swapping memory to disk, slowing down application execution. When you pass a variable into a method, a temporary table may provide better performance than a container.