Temporary tables

Temporary tables are used for non-persistent storage in Microsoft Dynamics Ax.

Introduction

From a developer's perspective, temporary tables store data in the same way as normal physical tables, except that the data is automatically dropped when no longer required.

They are useful in two common situations

1. As the datasource for a form or report, where the original data is too complex to be easily queried.
2. As temporary storage during complicated processing, to hold the results midway through the process.

Types of temporary tables

Prior to Dynamics Ax version Ax 2012, only one type of temporary table was available. In Ax 2012, however, the Temporary property on tables was replaced with a new property: TableType, which has three possible values:

• Regular - a standard physical table
• InMemory - the type of temporary table which existed in the previous versions of Dynamics Ax. Such tables are held in memory and written to a local disk file once they grow beyond a certain point
• TempDB - a new option in Ax 2012. They are "physical" temporary tables held in the SQL Server database.

The new TempDB tables operate in a similar manner to InMemory tables but support more features from standard physical tables:

• More powerful joins with physical tables are possible, and are properly supported by the database
• Can be per-company or global
• Support for normal tts transactions

Scoping rules

In general, each instance of a temporary table, and it's associated data, will only exist while the buffer variable used to access it is in scope.

You can point multiple buffer variables to the same instance of a temporary table. This is done differently depending on the type of temporary table.

• For InMemory tables, by using either the setTmpData() method or by directly assigning the buffers to each other, identically to normal tables.
• For TempDB tables, the linkPhysicalTableInstance() method replaces the setTmpData() call.

In this way, even if your original buffer variable goes out of scope, your data will be retained while one of the other referencing variables remains.

Be aware that static table methods - such as find() - will not work with temporary tables unless you pass through the buffer variable to the method.

For example, this method will not work on a temporary table, as the tempTable variable used is newly created and will always contain no records.

// This won't work on temporary table
public static TempTable find(AccountNum _accountNum, boolean _forUpdate = false)
{
    TempTable   tempTable;
    ;
 
    if (_accountNum)
    {
        tempTable.selectForUpdate(_forUpdate);
 
        select firstonly tempTable
        where tempTable.AccountNum      == _accountNum;
    }
 
    return tempTable;
}

If you want to have a find() method on your temporary table, then you will need to modify it slightly to pass through a reference to our populated temporary table.

// Use this pattern instead
public static TempTable find(AccountNum _accountNum, TempTable _tempTable, boolean _forUpdate = false)
{
 
    if (_accountNum)
    {
        _tempTable.selectForUpdate(_forUpdate);
 
        select firstonly _tempTable
        where _tempTable.AccountNum      == _accountNum;
    }
 
    return _tempTable;
}

Some examples of populating and using temporary tables can be found in File:TRG TempTablesGeneral.xpo project.

Creating temporary tables

In the AOT

This varies depending on temporary table type and Ax version:

• Prior to Ax 2012: Set the Temporary property to Yes to create a table which will always be temporary.
• In Ax 2012: Set the TableType property to either InMemory or TempDB

Note that any existing data will be permanently deleted if you do this!

Of course, you can no longer use the Table Browser to check the data, as the data is stored only per scoped instance of this table.

Making an existing table temporary

You can convert a normal table to a temporary table in code. For example, if you wish to create a temporary copy of the inventory table:

InventTable    inventTable;
;
 
inventTable.setTmp();

Doing so will remove all data from the temporary copy of the table. If you wish to create a populated temporary copy of a standard table, you can do the following:

InventTable    inventTable;
InventTable    inventTableTmp;
;
 
inventTableTmp.setTmp();
while select inventTable
{
   inventTableTmp.data(inventTable.data());
   inventTableTmp.doInsert();
}

You can now add, modify or delete data from the table without affecting the real contents stored in the database.

Important: When a buffer has been marked as temporary using the above method, assigning it to null, for example to clear out the values, will reset this and return the buffer to normal, non-temporary, operation.

Temporary tables in forms

Using temporary tables in forms requires the use of either the setTmpData() or linkPhysicalTableInstance() methods.

For example:

The temporary table data is populated in a static class method (running server side), which is called from the form and returns the populated table. We could populate a form-level buffer with the temporary data if needed, or else just call the populating method directly from the setTmpData()/linkPhysicalTableInstance() call as shown below.

In the form datasource init(), we use .setTmpData() to instruct the datasource query to use our temporary table. Our datasource name in this example is TempTable.

For Ax 2009 or InMemory tables

public void init()
{
  super(); 
 
  TempTable.setTmpData(tmpTableClass::populateTmpData());
}

See File:TRG TempTablesForm.xpo for an example of a working form based on a temporary table.

For Ax 2012+ TempDB tables

Note that this code does not currently work if the populateTmpData() method in our example is running server-side. However it is possible to pass through the TempTable datasource cursor to a server-side method and have it populated with code running on the AOS

public void init()
{
  super(); 
 
  TempTable.linkPhysicalTableInstance(tmpTableClass::populateTmpData());
}

It is also possible to add a table to a form which is not a temporary table, but the data that is shown must be temporary. Compared to the previous example there are not a lot of changes; just make sure the table is made temporary using .setTmp(). As in the previous example the temporary table data is populated in a static class method (running server side), which is called from the form and returns the populated table.

In the following example the InventTable is used.

public void init()
{
  super(); 
 
  InventTable.setTmp();
  InventTable.setTmpData(InventTableClass::populateTmpData());
}

Temporary tables in reports

The correct method of using temporary tables in X++ reports is slightly different from that of forms. In recent versions of Ax more and more reporting is handled in SSRS, and this has become less important.

The most important difference is the use of .setRecord() instead of .setTmpData(). A simple example follows:

public boolean fetch()
{
  boolean ret;
  ;
 
  this.queryRun().setRecord(tmpTableClass::populateTmpData());
 
  ret = super();
 
  return ret;
}

As there is often already a supporting RunBaseReport class being used to run the report, it is easy to integrate the population of the temporary data into that existing class. This is particularly useful if you need the data in the temporary table to be dependent on information entered into the report dialog prompt by the user.

See File:TRG TempTablesReports.xpo for an example of using a RunBaseReport class to run a report based on a temporary table.

Temporary table performance

Data being stored in InMemory temporary tables or prior to Ax 2009 is stored in a temporary physical file in the file system. The file itself is created when the first record is being inserted in that particular instance of the temporary table. Hence, in a 3 tier environment, the file will be maintained on server or client side, depending on where the first record is inserted. From a performance standpoint this is a concern when using temporary tables.

For TempDB tables, the data is stored in a physical table in the SQL Server database. This will be dropped automatically by the database when instructed by the AOS, and this occurs according to the normal scoping rules of temporary tables.

Indexes on temporary tables

As with normal tables, indexes can be created on temporary tables. When a temporary copy of a normal table is used with .setTmp(), then the existing indexes will also be created on the temporary version. For new temporary tables (with Temporary set to Yes or using the TableType property), you must create any desired indexes through the AOT in the normal way.

Indexes have a substantial effect on temporary table performance. For temporary tables with a lot of records you will experience major performance limitations when searching on non-indexed fields.

Security on temporary tables

You can assign a SecurityKey to a temporary table, like any other. The security key will work well, limiting access. However, temporary tables never show up in the tree for assigning permissions, so it's not possible to actually enable them for users. Therefore it's important not to put a security key on any temporary table or users will never be able to use it.

Database transactions (tts) on temporary tables

The beheviour of temporary tables inside database transactions depends on type:

InMemory tables

InMemory temporary tables are not included in Dynamics Ax's normal transaction processing capabilities. If you include population of a temporary table inside a ttsBegin/ttsCommit which then aborts, changes made to the temporary table will not be aborted.

To activate transaction capabilities on temporary tables, use the local ttsBegin and ttsCommit methods on the temporary table buffer themselves. These work as expected.

TempDB tables

TempDB tables operate exactly as normal physical tables, with full commit/roll-back support, and no special consideration is required when programming with these tables.