Temporary TempDB Tables

Article
08/03/2018

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

In Microsoft Dynamics AX, one type of temporary table is a TempDB table. We call them TempDB tables because their TableType property value is TempDB. This value comes from the TableType::TempDB enum value. The TableType property value can be set at AOT > Data Dictionary > Tables > MyTempDBTable > Properties > TableType.

All types of temporary tables are automatically dropped by the system when the table variable in X++ goes out of scope. A TempDB table is not dropped when you set its record buffer variable to null.

TempDB tables are a different type of temporary table than InMemory tables. For more information, see Temporary InMemory Tables.

Capabilities of TempDB Tables

The following table describes the capabilities of TempDB tables.

Capability	Description
Can be joined.	Your X++ SQL code can join a TempDB table to a regular table to perform multi-row operations in a single call.
Can be either per company or global.	The SaveDataPerCompany property on a TempDB table can be set to either Yes or No. The value of No makes it a global table. For more information, see Table Properties.
Can be used from Enterprise Portal by using .NET Business Connector.	TempDB tables can be used by forms, and in X++ code that is not tied to any user interface. But TempDB tables cannot be used directly by Enterprise Portal data sets.
Can have foreign key columns.	A TempDB table can have a foreign key column that references the primary key of another table. However, no table can have a foreign key column that references the primary key of a TempDB table.
TempDB tables can be instantiated from the client or server tier.	TempDB tables can be used by logic that runs on the client tier or the Application Object Server (AOS) tier.
Can have indexes columns.	A TempDB table can have indexes defined for it in the AOT.
Can have methods, but cannot override.	You can add methods to a TempDB table. However, you cannot override any methods that come with a new TempDB table.
Usable as a query How to: Add Multiple Data Sources to a Query.	A query under AOT > Queries can reference a TempDB table as a data source.
Transaction support.	The underlying database management system does provide transaction support for each instance of a TempDB table, just as it does for any regular table. Each instance is a separate table that is unrelated to the other instances, and each instance has a brief lifetime.
No configuration key.	No configuration key is needed for you to use a TempDB table. Also, tables are treated as TempDB tables while their configuration key is turned off.

Limitations of TempDB Tables

The following table describes the limitations of TempDB tables.

Limitation	Descriptions
Cannot be a valid time state table.	The ValidTimeStateFieldType property is restricted to the default value of None. This means the system cannot managed date effective data in a TempDB table.
Cannot have any delete actions.	A TempDB table cannot have any delete actions defined under its DeleteActions node in the AOT.
No Record Level Security (RLS).	RLS is not applied to TempDB tables.
Cannot use the Table browser form.	To add records to a regular table you can right-click its node in the AOT, and then click Open. This opens the Table browser form. By pressing the keys Ctrl+N, you can add records. However, the Table browser form is not supported for TempDB tables. This is because a TempDB table exists only during the scope of a method that instantiates it.
Cannot be in a table collection.	No type of temporary table can be in a table collection.
No view support.	No view in the AOT can reference a TempDB table. When a view is created under AOT > Data Dictionary > Views and is saved, the view is synchronized to the underlying database system. At that moment the view must be created by the database system. But each TempDB table is rarely present in the database, because the TempDB table exists only during the scope of the method that instantiates the table. Therefore, the TempDB table is usually not available in the database for any database view to reference the TempDB table.

Lifetime of TempDB Tables

A TempDB table is instantiated in the underlying database management system when the first SQL operation is sent to the database system from the AOS. The SQL operation can be either select, insert, update, or delete.

The following table describes the situations that cause a TempDB table to be dropped.

Cause of drop	Description
Variable goes out of scope.	The typical case is that a method declares a variable for the particular TempDB type. The method inserts data into the TempDB table. The insert causes the table to be instantiated in the database. When the method finishes, all variables declared in the method go out of scope. The AOS tells the database system to drop the TempDB table when its corresponding variable does out of scope.
Controlled restart of the AOS.	When you stop and restart the AOS, the AOS tells the database system to drop all TempDB tables.
Restart of the database system.	All TempDB table instances are dropped whenever the database management system is stopped and restarted. TempDB tables could be left in the underlying database if the AOS service ends suddenly, such as by a hardware power failure. There would be no automatic mechanism to promptly drop the tables.
Closure of the AX32.exe client.	Suppose that from your AX32.exe client you start a job from AOT > Jobs. The job calls a server method that instantiates a TempDB table. Then you close your AX32.exe program when the server method is still running. This ends your Microsoft Dynamics AX session and immediately ends the server method. All variables declared in the method are now out of scope. Therefore, the AOS tells the database system to drop the TempDB table.
Online Users form.	The Online Users form can be used to end any Microsoft Dynamics AX session that is associated with TempDB tables that have not been dropped automatically. The form is available in the content pane at Administration > Administration Area > Common forms > Online users.

TempDB Tables for Disabled Tables

You can disable a regular persisted database table by disabling the configuration key that controls the table. Disabling the key causes the system to automatically create a TempDB style of temporary table that matches the fields and schema of the database table. This temporary table exists in the underlying SQL Server database and is managed by the Application Object Server (AOS).

The purpose of automatically creating this TempDB table is to enable AOT objects that reference the disabled table to continue to compile and run. You can read and write to this TempDB table even though the configuration key is disabled.

All table buffer variables inherit the methods of the xRecord class. One such method is setTmp, which creates an InMemory temporary table that has the same schema as the regular table. However, the setTmp method cannot create an InMemory table from a TempDB table. You can call the method isTempDb to determine whether the setTmp method is available.

X++ Code Example

The following X++ code example assumes that a TempDB table which is named MyTempdb has already been defined under AOT > Data Dictionary > Tables. The MyTempdb table has one column that is the same type as the AccountNum column on the CustTable table. The while select statement in the example contains a JOIN clause that cannot be used with temporary InMemory tables.

    server public static void main(Args _args)
    {
        MyTempdb xrecMyTempdb;
        CustTable xrecCustTable;
        TableType tableTypeEnum;
        str stringWork;
    
        Global::info("Start of main.");
    
        xrecMyTempdb.AccountNum = "4004";
        xrecMyTempdb.doInsert();
        xrecMyTempdb.AccountNum = "4005";
        xrecMyTempdb.doInsert();
    
        tableTypeEnum = xrecMyTempdb.getTableType();
        stringWork = "MyTempdb.TableType is: " + enum2Str(tableTypeEnum);
        info(stringWork);
    
        while select *
                from xrecCustTable
                    JOIN xrecMyTempdb
                where
                    xrecMyTempdb.AccountNum == xrecCustTable.AccountNum
        {
            stringWork = xrecCustTable.AccountNum
                    + " , "
                    + int2Str(xrecCustTable.MandatoryCreditLimit);
            info(stringWork);
        }
    }
    /*** Infolog outputMessage (05:21:28 pm)
    Start of main.
    MyTempdb.TableType is: TempDB
    4004 , 0
    4005 , 1
    ***/